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

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

This is similar to this one here. The difference is I want to display the roots in the first column (to the left)

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	Entertainment	null
14	....	..

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), with the roots appearing at the first column:

root_id	root_name	child_id_1	child_name_1	child_id_2	child_name_2
1	Accommodation	3	Utility	9	internet
1	Accommodation	3	Utility	8	Gas
2	Transport	5	Private	12	Car repayment
2	Transport	6	Public	null	null
12	Entertainment	null	null	null	null
..	..	..	..	..	..	..

Solution

Similar to your other question; but this time start with identifying the root nodes:

With as (
select *
from MyTbl RN
where RN.parent_id is null
)
select  RN.*, C1.*,C2.*
from RootNodes RN
     left join
     MyTbl C1
     on RN.id=C1.parent_id
     left join
     MyTbl C2
     on C1.id=C2.parent_id