SQLTeam.com | Weblogs | Forums

Hierarchy - Rows to columns

#1

I have a hierarchy that looks like Figure 1: Note that E has 2 parents (B and C).

-- Figure 1: Parent Child Hierarchy

foo

-- 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

#2

Which version of Microsoft SQL Server are you working on?

#3

I'm on SQL 2016

#4

Try this:

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
;
#5

A BIG THANK YOU!!! This will work well for me.