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?
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')
;