sql 转置_sql进阶教程：case when用法之行列转置

问题描述：

长宽表格互换，将左侧df长表通过代码生成出右侧的宽表

select pref_name,
       sum(case when sex = '1' then population else 0 end) as 男, 
       sum(case when sex = '2' then population else 0 end) as 女
    from df 
group by pref_name

如需要按照各个县生成对男女的人数汇总，则使用：

SELECT sex,
       SUM(population) AS total,
       SUM(CASE WHEN pref_name = '德岛' THEN population ELSE 0 END) AS col_1,
       SUM(CASE WHEN pref_name = '香川' THEN population ELSE 0 END) AS col_2,
       SUM(CASE WHEN pref_name = '爱媛' THEN population ELSE 0 END) AS col_3,
       SUM(CASE WHEN pref_name = '高知' THEN population ELSE 0 END) AS col_4,
       SUM(CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知')
                THEN population ELSE 0 END) AS zaijie
  FROM PopTbl2
 GROUP BY sex;