[leetcode] 1795. Rearrange Products Table

 

https://leetcode.com/problems/reformat-department-table

 

행을 열로 바꾸는 pivot 을 사용한다.

MySQL 은 pivot 기능을 제공하지 않으므로, case when 문을 사용한다.

id 를 기준으로 생긴 그룹에서 month 가 특정 문자열이라는 조건에 맞는 revenue 를 추출한다.

조건에 맞는 revenue 는 하나 이상일 가능성이 있으므로, MAX를 사용하여 하나의 값만 추출하도록 한다.

(대개 MAX 혹은 SUM 이 사용된다)

 

 

SELECT id,
MAX(case when month = "Jan" then revenue else null end) as 'Jan_Revenue',
MAX(case when month = "Feb" then revenue else null end) as 'Feb_Revenue',
MAX(case when month = "Mar" then revenue else null end) as 'Mar_Revenue',
MAX(case when month = "Apr" then revenue else null end) as 'Apr_Revenue',
MAX(case when month = "May" then revenue else null end) as 'May_Revenue',
MAX(case when month = "Jun" then revenue else null end) as 'Jun_Revenue',
MAX(case when month = "Jul" then revenue else null end) as 'Jul_Revenue',
MAX(case when month = "Aug" then revenue else null end) as 'Aug_Revenue',
MAX(case when month = "Sep" then revenue else null end) as 'Sep_Revenue',
MAX(case when month = "Oct" then revenue else null end) as 'Oct_Revenue',
MAX(case when month = "Nov" then revenue else null end) as 'Nov_Revenue',
MAX(case when month = "Dec" then revenue else null end) as 'Dec_Revenue'
FROM Department
GROUP BY id;

 

 

 

---

 

< English (feedback by ChatGPT) >

 

We can use a pivot clause which transforms rows to columns.

(We can implement a pivot operation to transform rows into columns.)

 

MySQL doesn't support a pivot clause, so we use 'case when' statement.

(Since MySQL doesn't natively support pivot clauses, we use a CASE WHEN statement instead.)

 

we extract revenue under the condition that month is equals to a certain string from a group by id.

(We extract revenue values where the month column matches a specific string, grouped by id.)

 

Revenue under the condition could be more than 1, use MAX clause to get only one value.

(Because there may be more than one revenue entry that meets the condition, we use the MAX function to ensure only one value is returned.)

 

usually MAX and SUM are used for this

(In most cases, either MAX or SUM is used.)