ykhan ykhan - 3 months ago 14
SQL Question

SQL: Tree structure table select

I have following tree structure:

ROOT
-Invoices
-Documents
-Word
-Excel


Stored in following table:

LevelID;LevelParentID;LevelName

1001;-1;ROOT

1002;1001;Invoices

1003;1001;Documents

1004;1003;Word

1005;1003;Excel


'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
Declare @Table table (LevelID int,LevelParentID int,LevelName varchar(50))
Insert into @Table values
(1001,-1,'ROOT'),
(1002,1001,'Invoices'),
(1003,1001,'Documents'),
(1004,1003,'Word'),
(1005,1003,'Excel')


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

;with cteHB (LevelID,LevelParentID,Lvl,LevelName) as (
     Select LevelID
           ,LevelParentID
           ,Lvl=1
           ,LevelName 
      From  @Table 
      Where @Top = case when @Top<0 then LevelParentID else LevelID end
     Union All
     Select cteCD.LevelID
           ,cteCD.LevelParentID,cteHB.Lvl+1
           ,cteCD.LevelName 
      From  @Table cteCD 
      Join cteHB on cteCD.LevelParentID = cteHB.LevelID)
Select LevelID
      ,LevelParentID
      ,Lvl
      ,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
Comments