CODEWITHSUNDEEP
sqloracle-database
GroovyDude
GroovyDude

Reputation: 13

Oracle - Hierarchical Query with Multiple Columns

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)

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?

Upvotes: 1

Views: 1059

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

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  |

Upvotes: 1

Related Questions