1+ -- 1179. Reformat Department Table
2+ --
3+ -- SQL Schema
4+ -- Table: Department
5+ --
6+ -- +---------------+---------+
7+ -- | Column Name | Type |
8+ -- +---------------+---------+
9+ -- | id | int |
10+ -- | revenue | int |
11+ -- | month | varchar |
12+ -- +---------------+---------+
13+ -- (id, month) is the primary key of this table.
14+ -- The table has information about the revenue of each department per month.
15+ -- The month has values in ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"].
16+ --
17+ -- Write an SQL query to reformat the table such that there is a department id column and a revenue column for each month.
18+ --
19+ -- The query result format is in the following example:
20+ --
21+ -- Department table:
22+ -- +------+---------+-------+
23+ -- | id | revenue | month |
24+ -- +------+---------+-------+
25+ -- | 1 | 8000 | Jan |
26+ -- | 2 | 9000 | Jan |
27+ -- | 3 | 10000 | Feb |
28+ -- | 1 | 7000 | Feb |
29+ -- | 1 | 6000 | Mar |
30+ -- +------+---------+-------+
31+ --
32+ -- Result table:
33+ -- +------+-------------+-------------+-------------+-----+-------------+
34+ -- | id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
35+ -- +------+-------------+-------------+-------------+-----+-------------+
36+ -- | 1 | 8000 | 7000 | 6000 | ... | null |
37+ -- | 2 | 9000 | null | null | ... | null |
38+ -- | 3 | null | 10000 | null | ... | null |
39+ -- +------+-------------+-------------+-------------+-----+-------------+
40+ --
41+ -- Note that the result table has 13 columns (1 for the department id + 12 for the months).
42+
43+
44+ -- Write your MySQL query statement below
45+
46+ -- group by solution
47+ select id,
48+ max (case when month= ' Jan' null end)as ' Jan_Revenue'
49+ max (case when month= ' Feb' null end)as ' Feb_Revenue'
50+ max (case when month= ' Mar' null end)as ' Mar_Revenue'
51+ max (case when month= ' Apr' null end)as ' Apr_Revenue'
52+ max (case when month= ' May' null end)as ' May_Revenue'
53+ max (case when month= ' Jun' null end)as ' Jun_Revenue'
54+ max (case when month= ' Jul' null end)as ' Jul_Revenue'
55+ max (case when month= ' Aug' null end)as ' Aug_Revenue'
56+ max (case when month= ' Sep' null end)as ' Sep_Revenue'
57+ max (case when month= ' Oct' null end)as ' Oct_Revenue'
58+ max (case when month= ' Nov' null end)as ' Nov_Revenue'
59+ max (case when month= ' Dec' null end)as ' Dec_Revenue'
60+ from Department
61+ group by id