SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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')
;
13 rows affected

with r as(
  select ParentId HeadId,ParentId,ChildId,1 lvl,cast('Level_1' as varchar) LevelN
  from test t1
  where not exists(select 1 from test t2 where t2.ChildId=t1.ParentId)
  union all
  select r.HEadId,t.ParentId,t.ChildId,lvl+1 lvl,cast(concat('Level_',(lvl+1)) as varchar)
  from r inner join test t
    on r.ChildId=t.ParentId
)
select * from r;


HeadId
ParentId
ChildId
lvl
LevelN


PR001
PR001
CR001
1
Level_1

PR001
PR001
CR002
1
Level_1

PR001
PR001
CR003
1
Level_1

PR001
CR002
DR003
2
Level_2

PR001
DR003
ER003
3
Level_3

PR001
CR001
DR001
2
Level_2

PR001
CR001
DR002
2
Level_2

PR001
DR001
ER001
3
Level_3

PR001
ER001
FR001
4
Level_4

PR001
FR001
GR001
5
Level_5

PR001
GR001
HR001
6
Level_6

PR001
HR001
IR001
7
Level_7

PR001
IR001
JR001
8
Level_8


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 * -- HeadId,lvl,Level_1 ,Level_2,Level_3,Level_4,Level_5,Level_6
  --,path
  -- ,left(lag(path)over(order by path),len(path)) pp
  ,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;


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

HeadId	ParentId	ChildId	lvl	LevelN
PR001	PR001	CR001	1	Level_1
PR001	PR001	CR002	1	Level_1
PR001	PR001	CR003	1	Level_1
PR001	CR002	DR003	2	Level_2
PR001	DR003	ER003	3	Level_3
PR001	CR001	DR001	2	Level_2
PR001	CR001	DR002	2	Level_2
PR001	DR001	ER001	3	Level_3
PR001	ER001	FR001	4	Level_4
PR001	FR001	GR001	5	Level_5
PR001	GR001	HR001	6	Level_6
PR001	HR001	IR001	7	Level_7
PR001	IR001	JR001	8	Level_8