gpa gpa - 4 months ago 22
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


Here is SQLFiddle Link: LINK TO FIDDLE

Answer

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 "normalized"). 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
Comments