sql - Pivoting on dates to get roll ups of sums of nodes in a hierarchy -
imagine have table emps, containing parent-child relationship:
manager employee ------- ---------- null johnson johnson ketler ketler braun ketler cooper and table contracts, containing history of employees bargains:
date_of_contract employee amount_of_contract ---------------- ---------- ------------------ 25.03.2015 ketler 4 25.03.2015 braun 3 25.03.2015 cooper 2 25.03.2015 johnson 9 26.03.2015 ketler 1 26.03.2015 braun 4 26.03.2015 cooper 3 26.03.2015 johnson 6 27.03.2015 ketler 6 27.03.2015 braun 2 27.03.2015 cooper 5 27.03.2015 johnson 7 we proceed query see hierarchical sum of contracts 25.03.2015:
with t0 ( select e.manager, e.employee, c.date_of_contract, c.amount_of_contract emps e inner join contracts c on c.employee=e.employee date_of_contract = to_date('25.03.15') ), t1 ( select t.*, ( select sum(amount_of_contract) t0 p connect prior employee = manager start p.employee = t.employee ) tot, level lvl t0 t connect prior t.employee = t.manager start t.manager null ) select lpad(' ',2*(lvl-1)) || employee employee, tot tot_25_03_15 t1 t this query produces following result:
employee tot_25_03_15 --------- ------------ johnson 18 ketler 9 braun 3 cooper 2 the goal looking results possible days in contracts table in single query, in given example following:
employee tot_25_03_15 tot_26_03_15 tot_27_03_15 -------------- ------------- ------------- ------------- johnson 18 14 20 ketler 9 8 13 braun 3 4 2 cooper 2 3 5 p.s. here code create emps , contracts:
create table emps (manager varchar2(50), employee varchar2(50)); insert emps (manager, employee) values (null,'johnson'); insert emps (manager, employee) values ('johnson','ketler'); insert emps (manager, employee) values ('ketler','braun'); insert emps (manager, employee) values ('ketler','cooper'); create table contracts (date_of_contract date, employee varchar2(50), amount_of_contract number(12,0)); insert contracts (date_of_contract, employee, amount_of_contract) values (to_date('25.03.2015'),'ketler',4); insert contracts (date_of_contract, employee, amount_of_contract) values (to_date('25.03.2015'),'braun',3); insert contracts (date_of_contract, employee, amount_of_contract) values (to_date('25.03.2015'),'cooper',2); insert contracts (date_of_contract, employee, amount_of_contract) values (to_date('25.03.2015'),'johnson',9); insert contracts (date_of_contract, employee, amount_of_contract) values (to_date('26.03.2015'),'ketler',1); insert contracts (date_of_contract, employee, amount_of_contract) values (to_date('26.03.2015'),'braun',4); insert contracts (date_of_contract, employee, amount_of_contract) values (to_date('26.03.2015'),'cooper',3); insert contracts (date_of_contract, employee, amount_of_contract) values (to_date('26.03.2015'),'johnson',6); insert contracts (date_of_contract, employee, amount_of_contract) values (to_date('27.03.2015'),'ketler',6); insert contracts (date_of_contract, employee, amount_of_contract) values (to_date('27.03.2015'),'braun',2); insert contracts (date_of_contract, employee, amount_of_contract) values (to_date('27.03.2015'),'cooper',5); insert contracts (date_of_contract, employee, amount_of_contract) values (to_date('27.03.2015'),'johnson',7);
you can use pivot first pivot values , sums on individual dates
with pivoted ( select employee, "'25-mar-15'" s25_03_15, "'26-mar-15'" s26_03_15, "'27-mar-15'" s27_03_15 contracts pivot ( sum( amount_of_contract) date_of_contract in ('25-mar-15', '26-mar-15', '27-mar-15')) ) , t0 ( select e.manager, e.employee, c.s25_03_15, c.s26_03_15, c.s27_03_15 pivoted c inner join emps e on c.employee=e.employee ) , t1 ( select t.*, ( select sum(s25_03_15) t0 p connect prior employee = manager start p.employee = t.employee ) tot_25_03_15, ( select sum(s26_03_15) t0 p connect prior employee = manager start p.employee = t.employee ) tot_26_03_15, ( select sum(s27_03_15) t0 p connect prior employee = manager start p.employee = t.employee ) tot_27_03_15, level lvl t0 t connect prior t.employee = t.manager start t.manager null) select lpad(' ',2*(lvl-1)) || employee employee, tot_25_03_15 , tot_26_03_15 , tot_27_03_15 t1 t i'm not excited inline sql summing of nodes here's alternative uses sys_connect_by_path self join on like
with pivoted ( select employee, "'25-mar-15'" s25_03_15, "'26-mar-15'" s26_03_15, "'27-mar-15'" s27_03_15 contracts pivot ( sum( amount_of_contract) date_of_contract in ('25-mar-15', '26-mar-15', '27-mar-15')) ) , t0 ( select e.manager, e.employee, c.s25_03_15, c.s26_03_15, c.s27_03_15 pivoted c inner join emps e on c.employee=e.employee ) , t1 ( select t.*, sys_connect_by_path(employee, '/') path, level lvl t0 t connect prior t.employee = t.manager start t.manager null) select lpad(' ',2*(b.lvl-1)) || b.employee employee, sum(a.s25_03_15) tot_25_03_15, sum(a.s26_03_15) tot_26_03_15, sum(a.s27_03_15) tot_26_03_15 t1 inner join t1 b on a.path b.path || '%' group lpad(' ',2*(b.lvl-1)) || b.employee 
Comments
Post a Comment