How to get all child records of each self-assessment table record

Question

I have a table as shown below:

  ID |  ChildsID |  Name         
 -----------------------              
 1 NULL Name1
 2 1 Name2
 3 2 Name3
 4 3 Name4
 5 4 Name5
 6 5 Name6
 7 6 Name7
 8 7 Name8
 9 8 Name9
 10 9 Name10
 11 3 Name11

now I need a query that can get all possible children for each record that have at least one child, and returns a DataSet to me like this:

  ID |  ParentID |  Name |  Parentids         
 -----------------------------------------              
 1 NULL Name1 11,10,9,8,7,6,5,4,3,2
 2 1 Name2 11,10,9,8,7,6,5,4,3
 3 2 Name3 11,10,9,8,7,6,5,4
 4 3 Name4 10,9,8,7,6,5
 5 4 Name5 10,9,8,7,6
 6 5 Name6 10,9,8,7
 7 6 Name7 10.9.8
 8 7 Name8 10.9
 9 8 Name9 10 

Answer 1

Use this query.

Updated Result: Fiddler Demo

CREATE TABLE TABLE1 (ID INT, ParentID INT, NAME VARCHAR(10)); INSERT INTO TABLE1 VALUES(1, NULL, 'Name1'); INSERT INTO TABLE1 VALUES(2, 1, 'Name2'); INSERT INTO TABLE1 VALUES(3, 2, 'Name3'); INSERT INTO TABLE1 VALUES(4, 3, 'Name4'); INSERT INTO TABLE1 VALUES(5, 4, 'Name5'); INSERT INTO TABLE1 VALUES(6, 5, 'Name6'); INSERT INTO TABLE1 VALUES(7, 6, 'Name7'); INSERT INTO TABLE1 VALUES(8, 7, 'Name8'); INSERT INTO TABLE1 VALUES(9, 8, 'Name9'); INSERT INTO TABLE1 VALUES(10, 9, 'Name10'); INSERT INTO TABLE1 VALUES(11, 3, 'Name11'); SELECT ID, ParentID , Name, Child = STUFF(( SELECT ',' + CAST(B.ID AS VARCHAR(100)) FROm TABLE1 AS B WHERE ISNULL(A.ID, 0) < B.ID AND B.ID NOT IN( ISNULL((SELECT TOP 1 C.ID FROm TABLE1 AS C WHERE C.ParentID IN ( SELECT ParentID FROM TABLE1 WHERE ID <= A.ID) ORDER BY C.ID DESC), 0)) ORDER BY B.ID DESC FOR XML PATH (''), type).value('.', 'varchar(max)'), 1,1,'') FROm TABLE1 AS A 

Old result:

  SELECT ID, ParentID , Name, ParentIDs = STUFF(( SELECT ',' + CAST(B.ID AS VARCHAR(100)) FROm MyTable AS B WHERE ISNULL(A.ID, 0) < B.ID ORDER BY B.ID DESC FOR XML PATH (''), type).value('.', 'varchar(max)'), 1,1,'') FROm MyTable AS A 

Answer 2

You can try using this:

  SELECT a.*,x.ParentIDs FROM YourTable a CROSS APPLY (SELECT STUFF(CAST((SELECT ',' + CONVERT(VARCHAR,b.ID) FROM YourTable b WHERE b.ID > a.ID AND ISNULL(b.ParentID,0) > ISNULL(a.ParentID,0) ORDER BY ID DESC FOR XML PATH(''), TYPE) AS VARCHAR(MAX)),1,1,'') AS ParentIDs ) x WHERE x.ParentIDs IS NOT NULL