René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Pivot queries with Oracle

drop table t_;

create table t_ (
  nm Varchar2(20),
  pr Char    ( 7),
  vl Number  
);
insert into t_ values ('company 1','2003-06', 10);
insert into t_ values ('company 1','2003-07', 29);
insert into t_ values ('company 1','2003-08', 39);
insert into t_ values ('company 1','2003-09', 41);
insert into t_ values ('company 1','2003-10', 22);

insert into t_ values ('company 2','2003-06', 13);
insert into t_ values ('company 2','2003-07', 17);
insert into t_ values ('company 2','2003-08', 61);
insert into t_ values ('company 2','2003-09', 55);
insert into t_ values ('company 2','2003-10', 71);

insert into t_ values ('company 3','2003-06', 33);
insert into t_ values ('company 3','2003-07', 18);
insert into t_ values ('company 3','2003-08', 27);
insert into t_ values ('company 3','2003-09',  5);
insert into t_ values ('company 3','2003-10', 32);
select 
  nm,
  jul,
  aug,
  sep,
  jul+aug+sep "Total"
from (
  select
    nm,
    max(case when pr='2003-07' then vl else null end) jul,
    max(case when pr='2003-08' then vl else null end) aug,
    max(case when pr='2003-09' then vl else null end) sep
  from
    t_
  group by
    nm);
This query returns:
NM                          JUL        AUG        SEP      Total
-------------------- ---------- ---------- ---------- ----------
company 1                    29         39         41        109
company 2                    17         61         55        133
company 3                    18         27          5         50
See also pivot with pipelined functions and the pivot clause for an alternative ways to achieve pivot queries.