MySQL_조건문 CASE를 활용한 테이블 피봇

테이블 피봇팅: 테이블의 구조를 바꾸는 것

SELECT CASE 
           WHEN categoryID = 1 THEN price
           ELSE NULL
       END AS category1_price, *
FROM Products;

 

 

categoryID=1의 Price 평균을 구하고 싶다면?

SELECT AVG(CASE 
           WHEN categoryID = 1 THEN price
           ELSE NULL
       END) AS category1_price, *
FROM Products;

 

테이블 피봇팅

SELECT AVG(CASE WHEN categoryID = 1 THEN price ELSE NULL END) AS category1_price,
       AVG(CASE WHEN categoryID = 2 THEN price ELSE NULL END) AS category2_price,
       AVG(CASE WHEN categoryID = 3 THEN price ELSE NULL END) AS category3_price
FROM Products;

 

 

---

LEET CODE 1179. Reformat Department Table

Write an SQL query to reformat the table such that there is a department id column and a revenue column for each month.

 

 

풀이과정

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;