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

Can the levels be 2 or 1, and if yes, how would you represent them? — tinazmu
– tinazmu, Commented Nov 1, 2024 at 2:28
Are all branches of your hierarchy consisting of exactly three nodes? GrandParent (Root)->Parent->Children (Leaf). You don't, for example, have a path, like, Other->Other Expenses(leaf), where Other has no parent.. (levels are exactly 3, instead being levels are 3 at max ) — tinazmu
– tinazmu, Commented Nov 1, 2024 at 2:49
@tinazmu, got you, there could be less than 3. It could be 1, or 2, or 3. I added last row in the output as an example for 2 — Hawk
– Hawk, Commented Nov 1, 2024 at 3:02

tinazmu · Accepted Answer · 2024-11-01 03:17:55Z

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

answered Nov 1, 2024 at 3:17

tinazmu

5,5442 gold badges12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hawk Over a year ago

Thank you. It worked for me. I posted another similar question with slight change in the output: stackoverflow.com/q/79147054/2153567

MT0 · Accepted Answer · 2024-11-02 01:46:10Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related