How to find list of child nodes in one query in sql

Question

Hi, I have a table.

Create table ParentChildhierarchy (Parent varchar(50),Child varchar(50))

In this table, I inserted a few lines using the SQL script below.

insert into ParentChildhierarchy values('A','B')

insert into ParentChildhierarchy values('B','C')

insert into ParentChildhierarchy values('C','D')

insert into ParentChildhierarchy values('E','A')

insert into ParentChildhierarchy values('F','K')

Now I need one row query that can give me a list of all the child nodes. for example, for parent A, he must list B, C, D. for parent "B" he should list "C, D" for parent "C", he should list "D" for parent "D", he should list "Null" for parent "E", he should list down "A" for parent "F", it should list "K"

Answer 1

:

DECLARE @Parent VARCHAR(50) = 'A'
;WITH cte
     AS (SELECT *
         FROM   ParentChildhierarchy
         WHERE  parent = @Parent 
         UNION ALL
         SELECT a.*
         FROM   ParentChildhierarchy a
                JOIN cte b
                  ON a.parent = b.child)
SELECT *
FROM   cte 

: :

DECLARE @Parent VARCHAR(50) = 'A'
;WITH cte
     AS (SELECT *
         FROM   ParentChildhierarchy
         WHERE  parent = @Parent 
         UNION ALL
         SELECT a.*
         FROM   ParentChildhierarchy a
                JOIN cte b
                  ON a.parent = b.child)
SELECT STUFF((SELECT ',' + Child
              FROM   cte
              FOR XML PATH('')), 1, 1, '') 

SQLFiddle

2:

:

;WITH cte
     AS (SELECT *,
                Parent [TopParent]
         FROM   ParentChildhierarchy
         --WHERE  Parent = 'A'
         UNION ALL
         SELECT a.*,
                [TopParent]
         FROM   ParentChildhierarchy a
                JOIN cte b
                  ON a.parent = b.child)
SELECT [TopParent],
       STUFF((SELECT ',' + Child
              FROM   cte b
              WHERE  a.[TopParent] = b.[TopParent]
              FOR XML PATH('')), 1, 1, '')
FROM   cte a
GROUP  BY [TopParent] 

SQLFiddle

, :

;WITH cte
     AS (SELECT *,
                Parent [TopParent]
         FROM   ParentChildhierarchy
         --WHERE  Parent = 'A'
         UNION ALL
         SELECT a.*,
                [TopParent]
         FROM   ParentChildhierarchy a
                JOIN cte b
                  ON a.parent = b.child) SELECT [TopParent],
       STUFF((SELECT ',' + Child
              FROM   cte b
              WHERE  a.[TopParent] = b.[TopParent]
              FOR XML PATH('')), 1, 1, '')
FROM   cte a
GROUP  BY [TopParent]
UNION
SELECT Child,
       NULL
FROM   ParentChildhierarchy p
WHERE  NOT EXISTS (SELECT 1
                   FROM   ParentChildhierarchy c
                   WHERE  p.Child = c.Parent) 

SQLFiddle

Microsoft : https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx