DECLARE @tbl TABLE ( Id int ,ParentId int ) INSERT INTO @tbl ( Id, ParentId ) VALUES ( 0, NULL ) , ( 8, 0 ) , ( 12, 8 ) , ( 16, 12 ) , ( 17, 16 ) , ( 18, 17 ) , ( 19, 17 ) WITH abcd AS ( -- anchor SELECT id ,ParentID ,CAST(id AS VARCHAR(100)) AS [Path] FROM @tbl WHERE ParentId IS NULL UNION ALL --recursive member SELECT t.id ,t.ParentID ,CAST(a.[Path] + ',' + CAST( t.ID AS VARCHAR(100)) AS varchar(100)) AS [Path] FROM @tbl AS t JOIN abcd AS a ON t.ParentId = a.id ) SELECT Id ,ParentID ,[Path] FROM abcd WHERE Id NOT IN ( SELECT ParentId FROM @tbl WHERE ParentId IS NOT NULL )