CODEWITHSUNDEEP
sqlsql-server-2008hierarchical-data
Sunil
Sunil

Reputation: 21396

Order records so children appear just after parent in a multi-level hierarchy

I have a table of items that contains all records of a multi-level hierarchy. So, there is a top level item and it has items below it which in turn has items below it and so on; also, there will be many top level items in this table.

My goal is to return records from this table such that each parent is followed by records of its children ( these children can be in any order) and each of the children is then followed by its children ( these children can be in any order) just after it and so on. I have created a SQLFiddle for this at: SQLFiddle for this

I have tried to obtain this using following query, but the third level hierarchy items are not appearing just after their parent.

select Id, ParentId, ChildLevel, 
CASE WHEN ChildLevel = 0 THEN Id 
     WHEN ChildLevel = 1 then Id  
     WHEN ChildLevel >  1 THEN ParentId END Num
from Items order by UltimateParentId, ChildLevel, Num ;

Question

Is there some way of getting children appear just after its parent in above sql fiddle?

Script for creating this table with data is as below.

create table Items ( Id int , ParentId int , ChildLevel int,     UltimateParentId int);

--I could have multiple hierarchy level for each top level item
--right now I am populating data at multiple levels for only one top level item
--but the table will consist of many top level items having their own hierarchy records

insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (1,0,0,0);
insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (2,1,1,1);
insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (3,1,1,1);
insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (4,1,1,1);
insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (5,3,2,1);
insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (7,2,2,1);
insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (6,3,2,1);
insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (8,4,2,1);
insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (9,3,2,1);
insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (10,2,2,1);
insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (11,4,2,1);
insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (12,11,3,1);
insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (13,5,3,1);
insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (14,11,3,1);
insert into Items (Id, ParentId, ChildLevel, UltimateParentId) values (15,5,3,1);

Upvotes: 1

Views: 326

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81930

As Requested. The following can generate a 200K hier in just a couple of seconds

I should add... Where you see Row_Number() over (Order By ID) I used ID, but this can be any other field to determine the desired presentation sequence.

Example

Select *,Lvl=1,Seq=cast(1000000+Row_Number() over (Order By ID) as varchar(900)) Into #TempBld from Items Where  ParentID = 0

Declare @Cnt int=1
While @Cnt<=30
    Begin
        Insert Into #TempBld 
        Select A.*,Lvl=B.Lvl+1,Seq=B.Seq+' '+ cast(1000000+Row_Number() over (Order By A.ID) as varchar(25))
         From  Items A
         Join  #TempBld B on (B.Lvl=@Cnt and A.ParentID=B.ID)
        Set @Cnt=@Cnt+1
    End

Select ID,R1=cast(Row_Number() over (Order by Seq) as int),Seq Into #TempR1 From #TempBld
Create Clustered Index idx on #TempR1 (Seq)
Select A.ID,R2 = Max(B.R1) Into  #TempR2 From  #TempBld A Join #TempR1  B on B.Seq Like A.Seq+'%' Group By A.ID

Select B.R1
      ,C.R2
      ,A.Lvl 
      ,A.ID
      ,A.ParentID
 From  #TempBld A
 Join  #TempR1  B on (A.ID=B.ID)
 Join  #TempR2  C on (A.ID=C.ID)
 Order By 1

Returns

enter image description here

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81930

Since you are not using strings for the ID/Parent, you can leverage the hierarchyid data type.

The R1,R2 and HierPath are optional in the final select. The R1 and R2 indicate ownership ranges. This facilitates aggregation and selection wihout having to go recursive.

Example

;with cteP as (
      Select ID
            ,ParentId 
            ,HierID = convert(hierarchyid,concat('/',ID,'/'))
      From   Items 
      Where  ParentId=0
      Union  All
      Select ID  = r.ID
            ,ParentId  = r.ParentId 
            ,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.ID,'/'))
      From   Items r
      Join   cteP p on r.ParentId  = p.ID)
Select R1    = Row_Number() over (Order By HierID)
      ,R2    = Row_Number() over (Order By HierID) + -1 + (Select count(*) From cteP where HierID.ToString() like A.HierID.ToString()+'%')
      ,Lvl   = HierID.GetLevel()
      ,ID
      ,ParentId
      ,HierID
      ,HierPath = HierID.GetDescendant ( null , null ).ToString()   
 From cteP A
 Order By A.HierID

Returns

enter image description here

Upvotes: 1

Capitan Planet
Capitan Planet

Reputation: 165

Yes. Common Table Expresion (CTE) can be recursive. If You use CTE You need only parrent ID. Check out https://technet.microsoft.com/pl-pl/library/ms186243(v=sql.105).aspx

Upvotes: 0

Related Questions