Reformat Department Table LeetCode Solution

Last updated on October 9th, 2024 at 10:16 pm

This Leetcode problem Reformat Department Table LeetCode Solution is done in SQL.

List of all LeetCode Solution

Level of Question

Easy

Reformat Department Table LeetCode Solution

Reformat Department Table LeetCode Solution

Problem Statement

Column NameType
idint
revenueint
monthvarchar
Table: Department

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.

Example 1:
Input:

id revenue month
18000Jan
29000Jan
310000Feb
17000Feb
16000Mar
Department table:

Output:

id Jan_RevenueFeb_Revenue Mar_RevenueDec_Revenue
1800070006000null
29000nullnullnull
3null10000nullnull

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).

1. Reformat Department Table LeetCode Solution MySQL

select 
  id, 
  sum(
    if(month = 'Jan', revenue, null)
  ) as Jan_Revenue, 
  sum(
    if(month = 'Feb', revenue, null)
  ) as Feb_Revenue, 
  sum(
    if(month = 'Mar', revenue, null)
  ) as Mar_Revenue, 
  sum(
    if(month = 'Apr', revenue, null)
  ) as Apr_Revenue, 
  sum(
    if(month = 'May', revenue, null)
  ) as May_Revenue, 
  sum(
    if(month = 'Jun', revenue, null)
  ) as Jun_Revenue, 
  sum(
    if(month = 'Jul', revenue, null)
  ) as Jul_Revenue, 
  sum(
    if(month = 'Aug', revenue, null)
  ) as Aug_Revenue, 
  sum(
    if(month = 'Sep', revenue, null)
  ) as Sep_Revenue, 
  sum(
    if(month = 'Oct', revenue, null)
  ) as Oct_Revenue, 
  sum(
    if(month = 'Nov', revenue, null)
  ) as Nov_Revenue, 
  sum(
    if(month = 'Dec', revenue, null)
  ) as Dec_Revenue 
from 
  Department 
group by 
  id;
Scroll to Top