Sunday, April 28, 2013

Oracle How to pivot a table

create table winners
(year varchar(4) not null,
team varchar(30) not null,
constraint pk_winners primary key (year));

insert into winners (year,team) values ('1990','SAINTS');
insert into winners (year,team) values ('1991','SWANS');
insert into winners (year,team) values ('1992','DOGS');
insert into winners (year,team) values ('1993','MAGPIES');
insert into winners (year,team) values ('1994','TIGERS');


SQL> select * from winners;

YEAR TEAM
---- ------------------------------
1990 SAINTS
1991 SWANS
1992 DOGS
1993 MAGPIES
1994 TIGERS

5 rows selected.

SQL> select max(decode(year,'1990',team,'')) as "1990",
max(decode(year,'1991',team,'')) as "1991",
max(decode(year,'1992',team,'')) as "1992",
max(decode(year,'1993',team,'')) as "1993",
max(decode(year,'1994',team,'')) as "1994"
from winners

1990       1991       1992       1993       1994
---------- ---------- ---------- ---------- ----------
SAINTS     SWANS      DOGS       MAGPIES    TIGERS

1 row selected.

0 comments:

Post a Comment

Please Post your Comments..!