Reformat Department Table - Problem

You are given a table Department that contains information about the revenue of each department per month. The table has the following structure:

  • id: Department identifier (int)
  • revenue: Revenue amount for the department in a specific month (int)
  • month: Month name as a string (varchar)

The month column contains values from ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"].

Task: Reformat the table to create a pivot table where:

  • Each row represents a department (by id)
  • There is a separate column for each month showing the revenue
  • If a department has no revenue for a particular month, the value should be NULL

Return the result table in any order.

Table Schema

Department
Column Name Type Description
id PK int Department identifier
revenue int Revenue amount for the department in the specified month
month PK varchar Month name (Jan, Feb, Mar, etc.)
Primary Key: (id, month)
Note: Each row represents revenue for a specific department in a specific month

Input & Output

Example 1 — Basic Pivot
Input 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 Apr_Revenue May_Revenue Jun_Revenue Jul_Revenue Aug_Revenue Sep_Revenue Oct_Revenue Nov_Revenue Dec_Revenue
1 8000 7000 6000
2 9000
3 10000
💡 Note:

The input table shows revenue data for three departments across different months. Department 1 has revenue in Jan (8000), Feb (7000), and Mar (6000). Department 2 only has Jan revenue (9000). Department 3 only has Feb revenue (10000). The pivot transformation creates one row per department with separate columns for each month, filling NULL where no data exists.
Example 2 — Single Department
Input Table:
id revenue month
1 8000 Jan
1 7000 Feb
Output:
id Jan_Revenue Feb_Revenue Mar_Revenue Apr_Revenue May_Revenue Jun_Revenue Jul_Revenue Aug_Revenue Sep_Revenue Oct_Revenue Nov_Revenue Dec_Revenue
1 8000 7000
💡 Note:

This example shows a single department with revenue in only two months. The pivot creates one output row for department 1 with Jan_Revenue = 8000, Feb_Revenue = 7000, and NULL for all other months.

Constraints

  • 1 ≤ id ≤ 1000
  • 1 ≤ revenue ≤ 100000
  • month is one of ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
  • Each (id, month) combination appears at most once

Visualization

Tap to expand
SQL Pivot Transformation: Department RevenueInput: Long Formatidrevenuemonth18000Jan29000Jan17000FebPIVOTOutput: Wide FormatidJan_RevenueFeb_RevenueMar_Revenue...180007000null...29000nullnull...Key SQL PatternSELECT id, SUM(CASE WHEN month = 'Jan' THEN revenue END) AS Jan_Revenue, SUM(CASE WHEN month = 'Feb' THEN revenue END) AS Feb_Revenue, ...FROM Department GROUP BY id
Understanding the Visualization
1
Input
Row per department-month combination
2
Pivot
CASE WHEN transforms months to columns
3
Output
One row per department with month columns
Key Takeaway
🎯 Key Insight: Use CASE WHEN with GROUP BY to pivot row data into columns - essential for reporting and data analysis

Related Problems

Asked in
Amazon 15 Microsoft 12 Google 8
28.5K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen