pivot_clause [Oracle SQL]

 

create table pivot_ex (
  a  varchar2(10),
  b  number,
  c  char(1)
);

insert into pivot_ex values ('foo',  1, 'y');
insert into pivot_ex values ('foo',  2, 'y');
insert into pivot_ex values ('foo',  3, 'y');
insert into pivot_ex values ('foo',  4, 'y');
insert into pivot_ex values ('foo', 99, 'n');

insert into pivot_ex values ('bar',  9, 'y');
insert into pivot_ex values ('bar', 11, 'y');
insert into pivot_ex values ('bar',-42, 'n');

insert into pivot_ex values ('baz', 30, 'y');
insert into pivot_ex values ('baz', 70, 'n');

select
  foo, bar, baz
from pivot_ex
  pivot (
    sum(b) for a in ('foo' as foo, 'bar' as bar, 'baz' as baz)
  )
where c = 'y';

       FOO        BAR        BAZ
---------- ---------- ----------
        10         20         30

And now without a where clause:

select
  foo, bar, baz
from pivot_ex
  pivot (
    sum(b) for a in ('foo' as foo, 'bar' as bar, 'baz' as baz)
  )
;

C        FOO        BAR        BAZ
- ---------- ---------- ----------
y         10         20         30
n         99        -42         70
-----------------------------Example 2---------------------------------

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