如上圖所示的PopTbl2表,包含(pref_name,sex,population),現(xiàn)要求通過SQL代碼轉(zhuǎn)化成下圖所示的表:

(說明:全國表示上圖所有縣加一起,四國表示圖示主要的四個縣匯總值)

解法:

/* 轉(zhuǎn)換行列——在表頭里加入?yún)R總和四國 */

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 性別;