How to get all child of each records of a self-referenced table

Question

I have a table as 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 want a query that can get all possible child for each records which have at least a child and returns me a DataSet 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

Use a recursice CTE. http://stackoverflow.com/questions/19914472/cte-to-get-all-children-descendants-of-a-parent — Tim Schmelter, Dec 02 '14 at 10:11
One more example from my previous ans http://stackoverflow.com/questions/26030796/i-need-all-the-employee-under-a-supervisor/26030899#26030899 — AK47, Dec 02 '14 at 10:18
In the original table the Parent Id of ID = 1 is null. In the result set the Parent Ids of ID = 1 are 11,10,9,8,7,6,5,4,3,2. This doesn't make sense. — Giorgos Betsos, Dec 02 '14 at 11:03
@Nightmaresux sorry i edited the question, i mean childIDs instead of parentIDs in result table — Mohsen, Dec 03 '14 at 04:49
@GiorgosBetsos sorry i edited the question, i mean childIDs instead of parentIDs in result table — Mohsen, Dec 03 '14 at 04:50

Saravana Kumar · Accepted Answer · 2014-12-02T11:12:04.487

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

Not the correct answer. You are just assigning the higher values as childs for the lower value. For 5 - 10,9,8,7,6 are childs. But your answer show - 11,10,9,8,7,6 — Veera, Dec 02 '14 at 10:24
@Veera: I have updated the result. Check and reply the status. — Saravana Kumar, Dec 02 '14 at 11:12
@Kumar Thanks a lot my friend, your old query does not match my ideal result, but your update works correctly. any way thanks — Mohsen, Dec 03 '14 at 05:31

score 2 · Answer 2 · answered Dec 02 '14 at 12:22

You can try use this one:

  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

How to get all child of each records of a self-referenced table

2 Answers2