如上图所示的PopTbl2表,包含(pref_name,sex,population),现要求通过SQL代码转化成下图所示的表:

(说明:全国表示上图所有县加一起,四国表示图示主要的四个县汇总值)

解法:

/* 转换行列——在表头里加入汇总和四国 */

SELECT case when sex="1" then "男" else "女" end as 性别,

SUM(population) AS 全国,

SUM(CASE WHEN pref_name = '德岛' THEN population ELSE 0 END) AS 德岛,

SUM(CASE WHEN pref_name = '香川' THEN population ELSE 0 END) AS 香川,

SUM(CASE WHEN pref_name = '爱媛' THEN population ELSE 0 END) AS 爱媛,

SUM(CASE WHEN pref_name = '高知' THEN population ELSE 0 END) AS 高知,

SUM(CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知')

THEN population ELSE 0 END) AS 四国

FROM PopTbl2

GROUP BY 性别;