gpa - 1 year ago 57

SQL Question

I have following table structure. I would like to get sum at each level from TAB2.

TAB1 stores hierarchy in level columns.

`TAB1`

----- ----- ---- ----

KEY L1 L2 L3

---- ----- ----- ----

A A

B A B

C A B C

D A B D

TAB2

-----

KEY TC

---- ----

A 10

B 11

C 6

D 12

X 11

Expected Output:

KEY SUM

---- ----

A 39

B 29

C 6

D 12

X 11

Here is SQLFiddle Link: LINK TO FIDDLE

Answer Source

In the solution provided below (including the input data as factored subqueries), first I show how to use `unpivot`

and additional operations to normalize `tab1`

(the result is the factored subquery `n`

for "`n`

ormalized"). Then, if you had the data in normal form, the output could be obtained by a direct application of standard hierarchical querying as shown at the bottom of my code.

```
with
tab1 (key, L1, L2, L3) as (
select 'A', 'A', null, null from dual union all
select 'B', 'A', 'B' , null from dual union all
select 'C', 'A', 'B' , 'C' from dual union all
select 'D', 'A', 'B' , 'D' from dual
),
tab2 (key, TC) as (
select 'A', 10 from dual union all
select 'B', 11 from dual union all
select 'C', 6 from dual union all
select 'D', 12 from dual union all
select 'X', 11 from dual
),
unpiv (key, l, ancestor) as (
select key, to_number(substr(lv, 2)), ancestor from tab1
unpivot (ancestor for lv in (L1, L2, L3))
),
d (key, depth) as (
select key, max(l)
from unpiv
group by key
),
n (child, parent, TC) as (
select d.key, u.ancestor, tab2.TC
from unpiv u
right outer join d
on u.key = d.key and u.l = d.depth - 1
left outer join tab2
on d.key = tab2.key
)
SELECT key, sum(TC) as sum_TC
from (
select connect_by_root child as key, TC
from n
connect by prior child = parent
)
group by key
order by key;
```

Along the way, in `unpiv`

, I already had all the parent-child relationships, so I could have joined that directly with `tab2`

on `unpiv.key = tab2.key`

and summed `TC`

grouping by `ancestor`

(similar to MT0's solution). Instead, I wanted to demonstrate two separate steps: (1) normalizing `tab1`

and (2) how easy it is to use hierarchical queries on normalized tables.

**Output**:

```
KEY SUM_TC
--- ----------
A 39
B 29
C 6
D 12
```