[SOLVED] Transpose Hierarchical Table based on leaf nodes with nodes as first column

Transpose Hierarchical Table based on leaf nodes with nodes as first column

I have a hierarchical table categories as follows:

id	name	parent_id
1	Accommodation	null
2	Transport	null
3	Utility	1
4	Maintenance	1
5	Private	2
6	Public	2
7	Electricity	3
8	Gas	3
9	Internet	3
10	Garden service	4
11	Repairs	4
12	Car repayment	5
13	....	..

I want to transpose this to show one row for each leaf to be as follows (I know upfront the levels are 3 at max)

leaf_id	leaf_name	parent_id_1	parent_name_1	parent_id_2	parent_name_2
9	internet	3	Utility	1	Accommodation
8	Gas	3	Utility	1	Accommodation
12	Car repayment	5	Private	2	Transport
6	Public	2	Transport	null	null
..	..	..	..	..	..	..

I tried with the following query, but I just could not get it right (for example, couldn't get the parent's name, only id:

SELECT * FROM
(
  SELECT id, name ,parent_id, level l
  FROM categories
connect by prior parent_id = id
)
PIVOT
(
  max(id)  --pivot clause
  FOR l   --pivot_for_clause
  IN (1 parent_id_1, 2 parent_id_2, 3 parent_id_2)  --pivot_in_clause
)
;

Solution

This should work in most RDBMSs (without using specific SQL extensions, or more advanced functions):

With LeafNodes as (
select *
from MyTbl LN
where not exists 
     (select 1
      from MyTbl PL
      where PL.parent_id=LN.id)
)
select  LN.*, P1.*,P2.*
from LeafNodes LN
     left join
     MyTbl P1
     on P1.id=LN.parent_id
     left join
     MyTbl P2
     on P2.id=P1.parent_id