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

Question

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

Answer 1

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

Answer 2

In Oracle, you can do it in a single query without any joins using:

SELECT id         AS leaf_id,
       name       AS leaf_name,
       PRIOR id   AS parent_id1,
       PRIOR name AS parent_name1,
       CASE LEVEL WHEN 3 THEN CONNECT_BY_ROOT(id) END AS parent_id2,
       CASE LEVEL WHEN 3 THEN CONNECT_BY_ROOT(name) END AS parent_name2
FROM   categories
WHERE  CONNECT_BY_ISLEAF = 1
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id

Which, for the sample data:

CREATE TABLE categories (id, name, parent_id) AS
SELECT  1, 'Accommodation',  null FROM DUAL UNION ALL
SELECT  2, 'Transport',      null FROM DUAL UNION ALL
SELECT  3, 'Utility',        1 FROM DUAL UNION ALL
SELECT  4, 'Maintenance',    1 FROM DUAL UNION ALL
SELECT  5, 'Private',        2 FROM DUAL UNION ALL
SELECT  6, 'Public',         2 FROM DUAL UNION ALL
SELECT  7, 'Electricity',    3 FROM DUAL UNION ALL
SELECT  8, 'Gas',            3 FROM DUAL UNION ALL
SELECT  9, 'Internet',       3 FROM DUAL UNION ALL
SELECT 10, 'Garden service', 4 FROM DUAL UNION ALL
SELECT 11, 'Repairs',        4 FROM DUAL UNION ALL
SELECT 12, 'Car repayment',  5 FROM DUAL

Outputs:

LEAF_ID	LEAF_NAME	PARENT_ID1	PARENT_NAME1	PARENT_ID2	PARENT_NAME2
7	Electricity	3	Utility	1	Accommodation
8	Gas	3	Utility	1	Accommodation
9	Internet	3	Utility	1	Accommodation
10	Garden service	4	Maintenance	1	Accommodation
11	Repairs	4	Maintenance	1	Accommodation
12	Car repayment	5	Private	2	Transport
6	Public	2	Transport	null	null

fiddle