Problem

Table: Department

+————-+———+ | Column Name | Type | +————-+———+ | id | int | | revenue | int | | month | varchar | +————-+———+ In SQL,(id, month) is the primary key of this table. The table has information about the revenue of each department per month. The month has values in [“Jan”,“Feb”,“Mar”,“Apr”,“May”,“Jun”,“Jul”,“Aug”,“Sep”,“Oct”,“Nov”,“Dec”].

Reformat the table such that there is a department id column and a revenue column for each month.

Return the result table in any order.

The result format is in the following example.

Examples

Example 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

Input: 
Department table:
+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+
Output: 
+------+-------------+-------------+-------------+-----+-------------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+
Explanation: The revenue from Apr to Dec is null.
Note that the result table has 13 columns (1 for the department id + 12 for the months).

## Solution

### Method 1 – SQL Pivot (CASE WHEN)

#### Intuition

We want to transform rows into columns, so each department id has a column for each month's revenue. This is a classic SQL pivot problem.

#### Approach

1. For each month, use a CASE WHEN statement to select the revenue for that month, grouped by id.
2. Use aggregation (MAX or SUM) to collapse multiple rows per id into one.
3. Return all columns for all months.

#### Code



    

         
         
         
        
            
            
        
            
            
                 
                 
                
            
        
            
            
        
            
            
                 
                 
                
            
        
            
            
        
            
            
                 
                 
                
            
        
            
            
        
    


    

        
         
        
        
         
         
        

             
            
             
            
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
            
            
            




 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16




SELECT
  id,
  MAX(CASE WHEN month = 'Jan' THEN revenue END) AS Jan_Revenue,
  MAX(CASE WHEN month = 'Feb' THEN revenue END) AS Feb_Revenue,
  MAX(CASE WHEN month = 'Mar' THEN revenue END) AS Mar_Revenue,
  MAX(CASE WHEN month = 'Apr' THEN revenue END) AS Apr_Revenue,
  MAX(CASE WHEN month = 'May' THEN revenue END) AS May_Revenue,
  MAX(CASE WHEN month = 'Jun' THEN revenue END) AS Jun_Revenue,
  MAX(CASE WHEN month = 'Jul' THEN revenue END) AS Jul_Revenue,
  MAX(CASE WHEN month = 'Aug' THEN revenue END) AS Aug_Revenue,
  MAX(CASE WHEN month = 'Sep' THEN revenue END) AS Sep_Revenue,
  MAX(CASE WHEN month = 'Oct' THEN revenue END) AS Oct_Revenue,
  MAX(CASE WHEN month = 'Nov' THEN revenue END) AS Nov_Revenue,
  MAX(CASE WHEN month = 'Dec' THEN revenue END) AS Dec_Revenue
FROM Department
GROUP BY id;





        

        
        
         
        
        
         
         
        

             
            
             
            
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
            
            
            




 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16




SELECT
  id,
  MAX(CASE WHEN month = 'Jan' THEN revenue END) AS "Jan_Revenue",
  MAX(CASE WHEN month = 'Feb' THEN revenue END) AS "Feb_Revenue",
  MAX(CASE WHEN month = 'Mar' THEN revenue END) AS "Mar_Revenue",
  MAX(CASE WHEN month = 'Apr' THEN revenue END) AS "Apr_Revenue",
  MAX(CASE WHEN month = 'May' THEN revenue END) AS "May_Revenue",
  MAX(CASE WHEN month = 'Jun' THEN revenue END) AS "Jun_Revenue",
  MAX(CASE WHEN month = 'Jul' THEN revenue END) AS "Jul_Revenue",
  MAX(CASE WHEN month = 'Aug' THEN revenue END) AS "Aug_Revenue",
  MAX(CASE WHEN month = 'Sep' THEN revenue END) AS "Sep_Revenue",
  MAX(CASE WHEN month = 'Oct' THEN revenue END) AS "Oct_Revenue",
  MAX(CASE WHEN month = 'Nov' THEN revenue END) AS "Nov_Revenue",
  MAX(CASE WHEN month = 'Dec' THEN revenue END) AS "Dec_Revenue"
FROM Department
GROUP BY id;





        

        
        
         
        
        
         
         
        

             
            
             
            
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
                 
                     
                
            
            
            
            




1
2
3
4
5
6




# df is a pandas DataFrame with columns: id, revenue, month
result = df.pivot(index='id', columns='month', values='revenue')
months = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
result = result.reindex(columns=months)
result.columns = [f"{m}_Revenue" for m in months]
result = result.reset_index()





        

        
        
         
        
        
    





#### Complexity

* ⏰ Time complexity: `O(n)`, where n is the number of rows in the Department table, since each row is scanned once.
* 🧺 Space complexity: `O(d)`, where d is the number of unique department ids (for the output table).