sqlsql-serverhierarchy

Generate columns based on hierarchy


I have an Input table with two columns as below. The Parent ID and Child ID columns will have a hierarchical structure.

##Input Table

ParentID ChildID
PR001 CR001
PR001 CR002
PR001 CR003
CR001 DR001
CR001 DR002
CR002 DR003
DR001 ER001
DR002 ER002
....... ......
XXXXX YYYYY

I've applied recursive CTE method to generate the intermediate output to include level column based on the hierarchical structure

##Intermediate Output Table:

ParentID ChildID Level
PR001 CR001 1
PR001 CR002 1
PR001 CR003 1
CR001 DR001 2
CR001 DR002 2
CR002 DR003 3
DR001 ER001 3
DR002 ER002 3
....... ...... ...
XXXXX YYYYY N

I'm trying to generate the final output by pivoting the values based on the hierarchical structure of the intermediate output. The number of columns with level prefixed by its value will be based on the level column.

##Desired Result:

ID Level 1 Level 2 Level 3 Level....N
PR001 CR001 DR001 ER001
PR001 CR001 DR002 ER002
PR001 CR002 DR003
PR001 CR003
XXXX XXXX YYYY

Tried to apply pivot as below but it's not helping

SELECT ParentID, ChildID 
FROM 
(SELECT ParentID, ChildID, Level from table_1>) 
AS src 
PIVOT 
( 
count (ParentID) 
FOR [Level] 
IN ( '1' as 'Level_1', '2' as 'Level_2', '3' as 'Level_3' ]) 
) AS pvt ; 

Is there any functions to apply in SQL Server for this case?


Solution

  • I will propose a solution with recursion and a fixed number of columns (levels). Increasing the number of levels is not difficult.
    A solution with an unlimited number of levels can only be performed through a dynamic query.

    with r as(
      select ParentId HeadId,1 lvl,ParentId,ChildId
             ,cast(ChildId as varchar) Level_1
             ,cast('' as varchar) Level_2    ,cast('' as varchar) Level_3
             ,cast('' as varchar) Level_4    ,cast('' as varchar) Level_5
             ,cast('' as varchar) Level_6    ,cast('' as varchar) Level_7
             ,cast('' as varchar) Level_8
             ,cast(concat(ParentId,'-',ChildId) as varchar(1000)) path
      from test t1
      where not exists(select 1 from test t2 where t2.ChildId=t1.ParentId)
      union all
      select r.HEadId,lvl+1 lvl,t.ParentId,t.ChildId
         ,Level_1
         ,case when lvl=1 then cast(t.ChildId as varchar) else Level_2 end 
         ,case when lvl=2 then cast(t.ChildId as varchar) else Level_3 end 
         ,case when lvl=3 then cast(t.ChildId as varchar) else Level_4 end
         ,case when lvl=4 then cast(t.ChildId as varchar) else Level_5 end
         ,case when lvl=5 then cast(t.ChildId as varchar) else Level_6 end
         ,case when lvl=6 then cast(t.ChildId as varchar) else Level_7 end
         ,case when lvl=7 then cast(t.ChildId as varchar) else Level_8 end
         ,cast(concat(path,'-',t.ChildId) as varchar(1000)) path
      from r inner join test t on r.ChildId=t.ParentId
    )
    , tp as(
    select *
      ,case when path=left(lag(path)over(order by path desc),len(path)) then 0
       else 1 
       end fp
    from r
    )
    select HeadId,lvl,Level_1 ,Level_2,Level_3,Level_4,Level_5,Level_6,Level_7,Level_8
      ,path
    from tp
    where fp=1
    order by path;
    

    Output

    HeadId lvl Level_1 Level_2 Level_3 Level_4 Level_5 Level_6 Level_7 Level_8 path
    PR001 8 CR001 DR001 ER001 FR001 GR001 HR001 IR001 JR001 PR001-CR001-DR001-ER001-FR001-GR001-HR001-IR001-JR001
    PR001 2 CR001 DR002 PR001-CR001-DR002
    PR001 3 CR002 DR003 ER003 PR001-CR002-DR003-ER003
    PR001 1 CR003 PR001-CR003

    with test data

    Create Table test(ParentID NVARCHAR(10), ChildID  NVARCHAR(10));
    INSERT INTO test VALUES
     ('PR001', 'CR001')
    ,('PR001', 'CR002')
    ,('PR001', 'CR003')
    ,('CR001', 'DR001')
    ,('CR001', 'DR002')
    ,('CR002', 'DR003')
    ,('DR001', 'ER001')
    ,('DR003', 'ER003')
    ,('ER001', 'FR001')
    ,('FR001', 'GR001')
    ,('GR001', 'HR001')
    ,('HR001', 'IR001')
    ,('IR001', 'JR001')
    ;
    

    Test fiddle here