SQL Server : Recursive CTE (Hierarchy Example)

create table #temp
(
id int,
name varchar(30),
parent_id int
)

insert #temp ( id, name, parent_id )
values  ( 1, 'A', null )
, ( 2, 'B', null )
, ( 3, 'C', null )
, ( 4, 'AA', 1 )
, ( 5, 'BB', 2 )
, ( 6, 'AAA', 4 )
, ( 7, 'AAAA', 6 )

select * from #temp

image

;with cte (id,name,parent)
as
(
select id, name,cast('' as varchar(30)) as parent
from #temp
where parent_id is null

union all

select c1.id,c1.name,c2.name as parent
from #temp c1
join cte c2 on c1.parent_id = c2.id
)


select id,name,parent from cte

image