1179 重新格式化部门表

关键词：格式化 行转列 （id,month）联合主键

解法一：聚合函数 + case when then end + group by

select id,
    sum(case month when ‘Jan‘ then revenue end) as Jan_Revenue,
    sum(case month when ‘Feb‘ then revenue end) as Feb_Revenue,
    sum(case month when ‘Mar‘ then revenue end) as Mar_Revenue,
    sum(case month when ‘Apr‘ then revenue end) as Apr_Revenue,
    sum(case month when ‘May‘ then revenue end) as May_Revenue,
    sum(case month when ‘Jun‘ then revenue end) as Jun_Revenue,
    sum(case month when ‘Jul‘ then revenue end) as Jul_Revenue,
    sum(case month when ‘Aug‘ then revenue end) as Aug_Revenue,
    sum(case month when ‘Sep‘ then revenue end) as Sep_Revenue,
    sum(case month when ‘Oct‘ then revenue end) as Oct_Revenue,
    sum(case month when ‘Nov‘ then revenue end) as Nov_Revenue,
    sum(case month when ‘Dec‘ then revenue end) as Dec_Revenue
from Department
group by id

解法二：聚合函数 + if函数 + group by

select id,
    sum(if(month=‘Jan‘, revenue, null)) as Jan_Revenue,
    sum(if(month=‘Feb‘, revenue, null)) as Feb_Revenue,
    sum(if(month=‘Mar‘, revenue, null)) as Mar_Revenue,
    sum(if(month=‘Apr‘, revenue, null)) as Apr_Revenue,
    sum(if(month=‘May‘, revenue, null)) as May_Revenue,
    sum(if(month=‘Jun‘, revenue, null)) as Jun_Revenue,
    sum(if(month=‘Jul‘, revenue, null)) as Jul_Revenue,
    sum(if(month=‘Aug‘, revenue, null)) as Aug_Revenue,
    sum(if(month=‘Sep‘, revenue, null)) as Sep_Revenue,
    sum(if(month=‘Oct‘, revenue, null)) as Oct_Revenue,
    sum(if(month=‘Nov‘, revenue, null)) as Nov_Revenue,
    sum(if(month=‘Dec‘, revenue, null)) as Dec_Revenue
from Department
group by id

1179 重新格式化部门表

原文：https://www.cnblogs.com/dolphinriver/p/14603864.html