I have following table structure. I would like to get sum at each level from TAB2.
TAB1 stores hierarchy in level columns.
----- ----- ---- ----
KEY L1 L2 L3
---- ----- ----- ----
B A B
C A B C
D A B D
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
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.
KEY SUM_TC --- ---------- A 39 B 29 C 6 D 12