Oracle - Hierarchical Query with Multiple Columns

Question

I have just started to learn hierarchical queries to solve problems. Most of the examples available are based on the condition that there is a specific parent and you can connect to it using prior.

However, I have a table structure like below where Column A is parent of Column B and Column B is parent of Column C.

Column_A	Column_B	Column_C
SaaS	A Cloud	Test 1
SaaS	A Cloud	Test 2
SaaS	A Cloud	Test 3
SaaS	B Cloud	Test 1
SaaS	B Cloud	Test 2
SaaS	C Cloud	Test 1

Based on the above table, I would like to see if there are any techniques to achieve the below results.

SaaS (Top-Level)

A Cloud (Level 2)
- Test 1 (Level 3)
- Test 2
- Test 3
B Cloud
- Test 1
- Test 2
C Cloud

I am unable to use the starts with due to multiple columns and no specified way of identifying Column_A as the distinct parent.

CREATE TABLE TAB1 (COLUMN_A,COLUMN_B,COLUMN_C) AS
SELECT 'SaaS','A Cloud','Test 1' FROM DUAL
UNION ALL
SELECT 'SaaS','A Cloud','Test 2' FROM DUAL
UNION ALL
SELECT 'SaaS','A Cloud','Test 3' FROM DUAL
UNION ALL
SELECT 'SaaS','B Cloud','Test 1' FROM DUAL
UNION ALL
SELECT 'SaaS','B Cloud','Test 2' FROM DUAL
UNION ALL
SELECT 'SaaS','C Cloud','Test 1' FROM DUAL


select TAB1.*,LEVEL from TAB1
START WITH COLUMN_A='SaaS'
connect by prior  COLUMN_B = COLUMN_A AND COLUMN_C= COLUMN_B

Any ideas on how to achieve this?

It's perfectly possible to walk the graph the way you want, but what exactly do you want as a result? If it's a simple String, then what are those bullets? Or do you want something like HTML with tags? Please elaborate. — The Impaler
– The Impaler, Commented Mar 20, 2021 at 3:20
I am trying to display this data in a tree region on Oracle Apex. apex.oracle.com/pls/apex/apex_pm/r/ut/tree — GroovyDude
– GroovyDude, Commented Mar 20, 2021 at 9:09

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-03-20 05:39:30Z

1

Is it what are you looking for:

with newTab (ParentCol,ChildCol) as (
select distinct  null  ParentCol, Column_A ChildCol from Tab1 where Column_A='SaaS'
union all
select distinct Column_A ParentCol,Column_B ChildCol from Tab1
union all
select distinct Column_B,Column_C from Tab1),
cte (ParentCol,ChildCol,lvl) as (
select ParentCol, ChildCol , 1 as lvl from newTab where ParentCol is null
union all 
select  nt.ParentCol, nt.ChildCol , (cte.lvl+ 1) as lvl from  newTab nt inner join cte on nt.ParentCol=cte.ChildCol)
select * from cte

Output:

|PARENTCOL|CHILDCOL|LVL|
| -       |SaaS    |1  |
|SaaS     |A Cloud |2  |
|SaaS     |C Cloud |2  |
|SaaS     |B Cloud |2  |
|A Cloud  |Test 3  |3  |
|A Cloud  |Test 2  |3  |
|A Cloud  |Test 1  |3  |
|C Cloud  |Test 1  |3  |
|B Cloud  |Test 1  |3  |
|B Cloud  |Test 2  |3  |

answered Mar 20, 2021 at 5:39

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

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

2 Comments

GroovyDude Over a year ago

You sir are amazing. Thank you very much. I learnt something new today. I had hit a recursive error during actual implementation but was able to figure that out when I looked at the logic and removed the redundancy at level 3.

Kazi Mohammad Ali Nur Romel Over a year ago

That's great. Best wishes.

Collectives™ on Stack Overflow

Oracle - Hierarchical Query with Multiple Columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related