Pivot Queries

on

Problem

You have data in a query which you'd like to switch around, as you would do in a spreadsheet,
Priori to 11G this was cumbersome.

SELECT  *
  FROM  (SELECT job,
                sum(decode(deptno,10,sal)) DEPT10,
                sum(decode(deptno,20,sal)) DEPT20,
                sum(decode(deptno,30,sal)) DEPT30,
                sum(decode(deptno,40,sal)) DEPT40
           FROM scott.emp
       GROUP BY job)
ORDER BY 1;

Solution

Use the Pivot clause.

Original Query
select   b.name project_name , c.first_name||' '||c.last_name name, round(sum(hours+(minutes/60)),2) hours , to_char(to_date(substr(time_date,1,10),'YYYY-MM-DD'),'MON-YYYY')  month
from apex_ebs_extension.xxapex_tw_time_entries  a,
     apex_ebs_Extension.xxapex_tw_projects b,
     apex_ebs_extension.xxapex_tw_people c
where a.project_id = b.id
and a.person_id = c.id
group by b.name  , c.first_name||' '||c.last_name, to_char(to_date(substr(time_date,1,10),'YYYY-MM-DD'),'MON-YYYY')
order by  c.first_name||' '||c.last_name;


With the Pivot
select * from
(select   b.name project_name , c.first_name||' '||c.last_name name, round(sum(hours+(minutes/60)),2) hours , to_char(to_date(substr(time_date,1,10),'YYYY-MM-DD'),'MON-YYYY')  month
from apex_ebs_extension.xxapex_tw_time_entries  a,
     apex_ebs_Extension.xxapex_tw_projects b,
     apex_ebs_extension.xxapex_tw_people c
where a.project_id = b.id
and a.person_id = c.id
group by b.name  , c.first_name||' '||c.last_name, to_char(to_date(substr(time_date,1,10),'YYYY-MM-DD'),'MON-YYYY')
order by  c.first_name||' '||c.last_name)
pivot (sum(hours) for month in ('JAN-2016','FEB-2016','MAR-2016','APR-2016','MAY-2016','JUN-2016','JUL-2016','AUG-2016','SEP-2016','OCT-2016','NOV-2016','DEC-2016'))
order by project_name, name;


Acknowledgement

http://orafaq.com/wiki/PIVOT


Comments

Post a Comment