I have a hierarchy that looks like Figure 1: Note that E has 2 parents (B and C).
-- Figure 1: Parent Child Hierarchy
-- the desired output provides a "path" to each node as follows
--id level0 level1 level2
--1 A A A
--2 A B B
--3 A C C
--4 A B D
--5 A B E
--6 A C E
create table hierarchy
( id int identity(1,1),
parent varchar(50),
child varchar(50),
level int
)
insert into hierarchy
select null, 'A', 0 union all
select 'A','B',1 union all
select 'A','C',1 union all
select 'C','E', 2 union all
select 'B', 'D', 2 union all
select 'B','E',2
-- output
--id parent child level
--1 NULL A 0
--2 A B 1
--3 A C 1
--4 C E 2
--5 B D 2
--6 B E 2
select id
,child as level0
,child as level1
,child as level2
from hierarchy
where parent is null
union all
select b.id
,a.child as level0
,b.child as level1
,b.child as level2
from hierarchy as a
inner join hierarchy as b
on b.parent=a.child
and b.level=1
where a.parent is null
union all
select c.id
,a.child as level0
,b.child as level1
,c.child as level2
from hierarchy as a
inner join hierarchy as b
on b.parent=a.child
and b.level=1
inner join hierarchy as c
on c.parent=b.child
and c.level=2
where a.parent is null
;