Oracle 行轉列總結 Case When,Decode,PIVOT 三種方式

[size=medium]最近又碰到行專列問題了,當時不假思索用的是子查詢,做完後我詢問面試管行專列标正的寫法應該如何寫,他告訴我說應該用"Decode",索性我就總結一下,一共三種方式[/size]

--=========建表語句

CREATE TABLE populations(

country VARCHAR2(20),

sex INT,

populcation INT

);

--=========插入記錄

insert into populations (COUNTRY, SEX, POPULCATION)

values ('中國 ', 1, 340);

insert into populations (COUNTRY, SEX, POPULCATION)

values ('中國 ', 2, 260);

insert into populations (COUNTRY, SEX, POPULCATION)

values ('美國 ', 1, 45);

insert into populations (COUNTRY, SEX, POPULCATION)

values ('美國 ', 2, 55);

insert into populations (COUNTRY, SEX, POPULCATION)

values ('加拿大', 1, 51);

insert into populations (COUNTRY, SEX, POPULCATION)

values ('加拿大', 2, 49);

insert into populations (COUNTRY, SEX, POPULCATION)

values ('英國 ', 1, 40);

insert into populations (COUNTRY, SEX, POPULCATION)

values ('英國 ', 2, 60);

commit;

--=========三種方式

--------------------------------------

Decode 方式

SELECT a.COUNTRY AS "國家",

SUM(DECODE(a.SEX, 1, a.POPULCATION)) AS "男",

SUM(DECODE(a.SEX, 2, a.POPULCATION)) AS "女"

FROM populations a

GROUP BY a.COUNTRY

--------------------------------------

Case When 方式

SELECT a.COUNTRY AS "國家",

SUM(CASE

WHEN a.SEX = 1 THEN

a.POPULCATION

END) AS "男",

SUM(CASE

WHEN a.SEX = 2 THEN

a.POPULCATION

END) AS "女"

FROM populations a

GROUP BY a.COUNTRY

--------------------------------------

PIVOT 方式 11G特有的方式

SELECT *

FROM (SELECT a.COUNTRY AS "國家", a.SEX, a.POPULCATION FROM populations a)

PIVOT(

SUM(POPULCATION)

FOR SEX IN(1 AS "男",2 AS "女"))