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

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
See also this link and this link for alternative ways to to pivot queries.