|
| 1 | +/* |
| 2 | +Question 1179. Reformat Department Table |
| 3 | +Link: https://leetcode.com/problems/reformat-department-table/description/?envType=problem-list-v2&envId=database |
| 4 | +
|
| 5 | +Table: Department |
| 6 | +
|
| 7 | ++-------------+---------+ |
| 8 | +| Column Name | Type | |
| 9 | ++-------------+---------+ |
| 10 | +| id | int | |
| 11 | +| revenue | int | |
| 12 | +| month | varchar | |
| 13 | ++-------------+---------+ |
| 14 | +In SQL,(id, month) is the primary key of this table. |
| 15 | +The table has information about the revenue of each department per month. |
| 16 | +The month has values in ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]. |
| 17 | +
|
| 18 | +
|
| 19 | +Reformat the table such that there is a department id column and a revenue column for each month. |
| 20 | +
|
| 21 | +Return the result table in any order. |
| 22 | +*/ |
| 23 | + |
| 24 | +SELECT |
| 25 | + id, |
| 26 | +SUM(CASE WHEN month='Jan' THEN revenue END)AS Jan_Revenue, |
| 27 | +SUM(CASE WHEN month='Feb' THEN revenue END)AS Feb_Revenue, |
| 28 | +SUM(CASE WHEN month='Mar' THEN revenue END)AS Mar_Revenue, |
| 29 | +SUM(CASE WHEN month='Apr' THEN revenue END)AS Apr_Revenue, |
| 30 | +SUM(CASE WHEN month='May' THEN revenue END)AS May_Revenue, |
| 31 | +SUM(CASE WHEN month='Jun' THEN revenue END)AS Jun_Revenue, |
| 32 | +SUM(CASE WHEN month='Jul' THEN revenue END)AS Jul_Revenue, |
| 33 | +SUM(CASE WHEN month='Aug' THEN revenue END)AS Aug_Revenue, |
| 34 | +SUM(CASE WHEN month='Sep' THEN revenue END)AS Sep_Revenue, |
| 35 | +SUM(CASE WHEN month='Oct' THEN revenue END)AS Oct_Revenue, |
| 36 | +SUM(CASE WHEN month='Nov' THEN revenue END)AS Nov_Revenue, |
| 37 | +SUM(CASE WHEN month='Dec' THEN revenue END)AS Dec_Revenue |
| 38 | +FROM Department |
| 39 | +GROUP BY id |