Hierarchical Query: Roll-up leaf nodes to ancestors.

This query may be more applicable to a bill of materials type of query. I’ve added a new column to the EMP table (value number). Then I only populated a value for this column for the leaf nodes, i.e. the employees lowest in the hierarchy (ADAMS, SMITH, ALLEN, WARD, MARTIN, TURNER, JAMES, MILLER).

This query the sums up these leaf node values to their manager, and to the manager’s manager.

[sourcecode language=”sql”]

with totals as (
select empno
,ename
,mgr
,value
,level lev
,rownum rn
,sum(nvl(value, 0))
over(
partition by connect_by_root(empno)
) tot_val
from emp t
connect by prior empno=mgr
)

, lev1 as (
select empno
,mgr
,rn
,ename
,value
,tot_val
from totals
where lev = 1
)

select lpad(‘ ‘, 2*level, ‘ ‘)
||ename name
,value
,tot_val
from lev1
connect by prior empno=mgr
start with mgr is null

[/sourcecode]

Hierarchy Rollup

Leave a Reply

Your email address will not be published. Required fields are marked *