STORM STORM - 3 months ago 5
SQL Question

How to all possible child rows in a SQL table when using id parentid relationship in single table

I have the following table schema:

ID | PARENT ID | NAME
-------------------------
1 | NULL | A - ROOT
2 | 1 | B
3 | 2 | C
4 | 1 | D
5 | 4 | E
6 | 5 | F


The hierarchy look like:

A
-- B
-- -- C
-- D
-- -- E
-- -- -- F


I want to get all child recursively in all descendant levels.

For example when I have
A
and query for it, I would like to get back
A, B, C, D, E
and
F
.

When I have
E
I want to get
E
and
F
.

When I have
D
I want to get
D, E
and
F
.

I am not SQL expert and as a developer normally I would build programmatically loops with DB query and check whether I have children or not and recursively get the children. But this i definitely a very expensive/unperformant approach.

Is there an elegant/better way by using a SQL statement?

Answer

Here is a generic hierarchy build. This one will maintain presentation sequence and also includes RANGE KEYS (optional and easy to remove if not needed)

Declare @YourTable table (ID int,Parent_ID int,Name varchar(50))
Insert into @YourTable values (1,null,'A - Root'),(2,1,'B'),(3,2,'C'),(4,1,'D'),(5,4,'E'),(6,5,'F')


Declare @Top  int = null          --<<  Sets top of Hier Try 4
Declare @Nest varchar(25) ='   '  --<<  Optional: Added for readability

;with cteHB (Seq,ID,Parent_ID,Lvl,Name) as (
    Select  Seq  = cast(1000+Row_Number() over (Order by Name) as varchar(500))
           ,ID
           ,Parent_ID
           ,Lvl=1
           ,Name 
     From   @YourTable 
     Where  IsNull(@Top,-1) = case when @Top is null then isnull(Parent_ID,-1) else ID end
     Union  All
     Select Seq  = cast(concat(cteHB.Seq,'.',1000+Row_Number() over (Order by cteCD.Name)) as varchar(500))
           ,cteCD.ID
           ,cteCD.Parent_ID,cteHB.Lvl+1
           ,cteCD.Name 
     From   @YourTable cteCD 
     Join   cteHB on cteCD.Parent_ID = cteHB.ID)
    ,cteR1 as (Select Seq,ID,R1=Row_Number() over (Order By Seq) From cteHB)
    ,cteR2 as (Select A.Seq,A.ID,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.ID )
Select B.R1  
      ,C.R2
      ,A.ID
      ,A.Parent_ID
      ,A.Lvl
      ,Name = Replicate(@Nest,A.Lvl) + A.Name
 From cteHB A
 Join cteR1 B on A.ID=B.ID
 Join cteR2 C on A.ID=C.ID
 Order By B.R1              --<< Or use A.Seq

Returns

R1  R2  ID  Parent_ID   Lvl    Name
1   6   1   NULL        1      A - Root
2   3   2   1           2         B
3   3   3   2           3            C
4   6   4   1           2         D
5   6   5   4           3            E
6   6   6   5           4               F