CODEWITHSUNDEEP
sqloracle-database
gpa
gpa

Reputation: 2451

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

Upvotes: 1

Views: 1272

Answers (2)

user5683823
user5683823

Reputation:

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

Upvotes: 1

MT0
MT0

Reputation: 168741

Oracle Setup

Create table TAB1 (pKey varchar2(10),level1 varchar2(10),level2 varchar2(10),level3 varchar2(10),level4 varchar2(10));
insert into TAB1(pKey,level1) values('A','A');
insert into TAB1(pKey,level1,level2) values('B','A','B');
insert into TAB1(pKey,level1,level2,level3) values('C','A','B','C');
insert into TAB1(pKey,level1,level2,level3) values('D','A','B','D');

Create table TAB2 (pKey varchar(10), tc integer);
insert into TAB2(pKey,tc) values('A',10);
insert into TAB2(pKey,tc) values('B',11);
insert into TAB2(pKey,tc) values('C',6);
insert into TAB2(pKey,tc) values('D',12);
insert into TAB2(pKey,tc) values('X',11);

Query:

SELECT t2.pKey,
       SUM( COALESCE( t4.TC, t2.tc ) ) AS tc
FROM   tab2 t2
       LEFT OUTER JOIN
       tab1 t1
       ON ( t2.pKey = t1.pKey )
       LEFT OUTER JOIN
       tab1 t3
       ON (    t1.level1 = t3.level1
           AND ( t1.level2 IS NULL OR t1.level2 = t3.level2 )
           AND ( t1.level3 IS NULL OR t1.level3 = t3.level3 )
           AND ( t1.level4 IS NULL OR t1.level4 = t3.level4 ) )
       LEFT OUTER JOIN
       tab2 t4
       ON ( t3.pKey = t4.pKey )
GROUP BY t2.pKey;

Output:

PKEY               TC
---------- ----------
D                  12 
A                  39 
B                  29 
C                   6 
X                  11

Upvotes: 1

Related Questions