ykhan ykhan - 1 year ago 136
SQL Question

SQL: Tree structure table select

I have following tree structure:


Stored in following table:







'Invoices' and 'Documents' are the main levels under ROOT (1001) level. And sub levels 'Word' and 'Excel' are under Documents (1003) level.

I want to select all rows under a particular level. For example, how to:

  1. select whole tree?

  2. select all levels under 'Documents' level?

Answer Source
Declare @Table table (LevelID int,LevelParentID int,LevelName varchar(50))
Insert into @Table values

Declare @Top  int = -1            -- 
Declare @Nest varchar(25) ='   '  -- Optional: Added for readability

;with cteHB (LevelID,LevelParentID,Lvl,LevelName) as (
     Select LevelID
      From  @Table 
      Where @Top = case when @Top<0 then LevelParentID else LevelID end
     Union All
     Select cteCD.LevelID
      From  @Table cteCD 
      Join cteHB on cteCD.LevelParentID = cteHB.LevelID)
Select LevelID
      ,LevelName = Replicate(@Nest,Lvl) + LevelName
 From cteHB

Returns when @Top=-1

LevelID LevelParentID   Lvl    LevelName
1001    -1              1      ROOT
1002    1001            2         Invoices
1003    1001            2         Documents
1004    1003            3            Word
1005    1003            3            Excel

Returns when @Top=1003

LevelID LevelParentID   Lvl   LevelName
1003    1001            1      Documents
1004    1003            2         Word
1005    1003            2         Excel