1179. Reformat Department Table
Problem Statement
Reformat the table so that there is a department id column and a revenue column for each month, in the order Jan_Revenue, Feb_Revenue, ..., Dec_Revenue. Return the result in any order.
Table Schema
Table: Department
+-------------+---------+
| id | int |
| revenue | int |
| month | varchar |
+-------------+---------+
(id, month) is the primary key. month can be one of Jan, Feb, ..., Dec.
Examples
Example 1
Input Table:
Department table:
+----+---------+-------+
| id | revenue | month |
+----+---------+-------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 1 | 7000 | Feb |
+----+---------+-------+
Expected Output:
+----+-------------+-------------+-----+
| id | Jan_Revenue | Feb_Revenue | ... |
+----+-------------+-------------+-----+
| 1 | 8000 | 7000 | ... |
| 2 | 9000 | null | ... |
+----+-------------+-------------+-----+
SQL Solution
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;
Problem Info
DifficultyEASY
Topics
pivotconditional-aggregationgroup-by
Reference Links