LeetCode/SQL

leetcode 1179. Reformat Department Table

챠오위 2021. 1. 18. 14:40

 

 

1) CASE WHEN

 

1-1)

SELECT id
        , CASE WHEN month = "Jan" THEN revenue ELSE NULL END AS Jan_Revenue 
        , CASE WHEN month = "Feb" THEN revenue ELSE NULL END AS Feb_Revenue 
        , CASE WHEN month = "Mar" THEN revenue ELSE NULL END AS Mar_Revenue 
        , CASE WHEN month = "Apr" THEN revenue ELSE NULL END AS Apr_Revenue 
        , CASE WHEN month = "May" THEN revenue ELSE NULL END AS May_Revenue 
        , CASE WHEN month = "Jun" THEN revenue ELSE NULL END AS Jun_Revenue 
        , CASE WHEN month = "Jul" THEN revenue ELSE NULL END AS Jul_Revenue 
        , CASE WHEN month = "Aug" THEN revenue ELSE NULL END AS Aug_Revenue 
        , CASE WHEN month = "Sep" THEN revenue ELSE NULL END AS Sep_Revenue 
        , CASE WHEN month = "Oct" THEN revenue ELSE NULL END AS Oct_Revenue 
        , CASE WHEN month = "Nov" THEN revenue ELSE NULL END AS Nov_Revenue
        , CASE WHEN month = "Dec" THEN revenue ELSE NULL END AS Dec_Revenue 
FROM Department

 

1-1) 결과

{"headers": ["id", "Jan_Revenue", "Feb_Revenue", "Mar_Revenue", "Apr_Revenue", "May_Revenue", "Jun_Revenue", "Jul_Revenue", "Aug_Revenue", "Sep_Revenue", "Oct_Revenue", "Nov_Revenue", "Dec_Revenue"],

"values": [[1, 8000, null, null, null, null, null, null, null, null, null, null, null], [2, 9000, null, null, null, null, null, null, null, null, null, null, null], [3, null, 10000, null, null, null, null, null, null, null, null, null, null], [1, null, 7000, null, null, null, null, null, null, null, null, null, null], [1, null, null, 6000, null, null, null, null, null, null, null, null, null]]}

 

 

1-2) GROUP BY

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

 

1-2) 결과

{"headers": ["id", "Jan_Revenue", "Feb_Revenue", "Mar_Revenue", "Apr_Revenue", "May_Revenue", "Jun_Revenue", "Jul_Revenue", "Aug_Revenue", "Sep_Revenue", "Oct_Revenue", "Nov_Revenue", "Dec_Revenue"],

"values": [[1, 8000, null, null, null, null, null, null, null, null, null, null, null], [2, 9000, null, null, null, null, null, null, null, null, null, null, null], [3, null, 10000, null, null, null, null, null, null, null, null, null, null]]}

 

 

 

2) SUM

 

2-1)

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

 

2-1) 결과

{"headers": ["id", "Jan_Revenue", "Feb_Revenue", "Mar_Revenue", "Apr_Revenue", "May_Revenue", "Jun_Revenue", "Jul_Revenue", "Aug_Revenue", "Sep_Revenue", "Oct_Revenue", "Nov_Revenue", "Dec_Revenue"],

"values": [[1, 8000, 7000, 6000, null, null, null, null, null, null, null, null, null], [2, 9000, null, null, null, null, null, null, null, null, null, null, null], [3, null, 10000, null, null, null, null, null, null, null, null, null, null]]}

 

 

SUCCESS!!