gpa - 1 year ago 83
SQL Question

# Hierarchy query sum at each level

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
``````

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download