Hierarchical Query: Roll-up leaf nodes to ancestors.June 26, 2015 at 11:54 am | Posted in Oracle Developement | Leave a comment
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.
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