行转列- Case When,Decode,PIVOT

案例驱动

--建表
CREATE TABLE btr.btr.populations( 
country VARCHAR2(20), 
sex INT, 
populcation INT 
); 
--插入数据
insert into btr.populations (COUNTRY, SEX, POPULCATION) 
values ('中国  ', 1, 340); 
insert into btr.populations (COUNTRY, SEX, POPULCATION) 
values ('中国  ', 2, 260); 
insert into btr.populations (COUNTRY, SEX, POPULCATION) 
values ('美国  ', 1, 45); 
insert into btr.populations (COUNTRY, SEX, POPULCATION) 
values ('美国  ', 2, 55); 
insert into btr.populations (COUNTRY, SEX, POPULCATION) 
values ('加拿大', 1, 51); 
insert into btr.populations (COUNTRY, SEX, POPULCATION) 
values ('加拿大', 2, 49); 
insert into btr.populations (COUNTRY, SEX, POPULCATION) 
values ('英国  ', 1, 40); 
insert into btr.populations (COUNTRY, SEX, POPULCATION) 
values ('英国  ', 2, 60); 

--------------------------------------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 btr.populations a 
GROUP BY a.COUNTRY 
--------------------------------------decode 方式-------------------------------------------------------------
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 
--------------------------------------PIVOT 方式 11G特有的方式 ----------------------------------------

SELECT
country,男,女
  FROM (
            SELECT a.COUNTRY , a.SEX, a.POPULCATION 
             FROM btr.populations a) 
  PIVOT( SUM(POPULCATION) FOR SEX IN(1 AS 男 ,2 AS 女 ))

资料来源：http://albb0608.iteye.com/blog/1218737
decode详解：http://www.jianshu.com/p/3097b8bcc901