1179. Reformat Department Table | Easy

문제

 

Reformat the table such that there is a department id column and a revenue column for each month. Return the result table in any order.

테이블 정보

 

 

Solution

 

✅ 제공 테이블의 id, month, revenue 데이터를 가지고, 각 month를 열로 변환하여 revenue 데이터를 각 열에 맞게 매핑해야 하는 문제이다.

 

👉 CASE문과 집계 함수(MAX, SUM, ... )를 조합하여 피봇 테이블을 구현한다.

 

● 1 

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

 

💡동작원리

- id = 1 인 원본 row

1	8000	Jan
1	7000	Feb
1	6000	Mar

 

- CASE 구문 동작

1	8000
1	NULL
1	NULL

 

- MAX 집계함수 & GROUP BY 동작

1

8000

 

● 2

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

 

● 3

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