[LeetCode] Reformat Department Table

SELECT id, SUM(CASE WHEN month = "Jan" THEN revenue END) AS Jan_Revenue
         , SUM(CASE WHEN month = "Feb" THEN revenue END) AS Feb_Revenue
         , SUM(CASE WHEN month = "Mar" THEN revenue END) AS Mar_Revenue
         , SUM(CASE WHEN month = "Apr" THEN revenue END) AS Apr_Revenue
         , SUM(CASE WHEN month = "May" THEN revenue END) AS May_Revenue
         , SUM(CASE WHEN month = "Jun" THEN revenue END) AS Jun_Revenue
         , SUM(CASE WHEN month = "Jul" THEN revenue END) AS Jul_Revenue
         , SUM(CASE WHEN month = "Aug" THEN revenue END) AS Aug_Revenue
         , SUM(CASE WHEN month = "Sep" THEN revenue END) AS Sep_Revenue
         , SUM(CASE WHEN month = "Oct" THEN revenue END) AS Oct_Revenue
         , SUM(CASE WHEN month = "Nov" THEN revenue END) AS Nov_Revenue
         , SUM(CASE WHEN month = "Dec" THEN revenue END) AS Dec_Revenue
FROM department
GROUP BY id;

case문으로 피봇

month로 조건을 걸어 그 달의 revenue가 나오고 sum으로 집계를 하여 값이 없다면 null로 채워지게 만듦

그리고 as로 컬럼명을 변경

id별로 집계를 해야함으로 group by 사용

 

(문제 출처 - https://leetcode.com/problemset/database/)