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
Reformat Department Table - Pivot Solution INPUT TABLE id revenue month 1 8000 Jan 2 9000 Jan 3 10000 Feb 1 7000 Feb 1 6000 Mar 2 12000 Feb Rows grouped by dept months scattered Dept 1 Dept 2 Dept 3 Multiple rows per dept Need to pivot months ALGORITHM STEPS 1 GROUP BY id Aggregate rows by dept 2 CASE for each month Create column per month 3 SUM aggregation Get revenue per month 4 NULL for missing No data returns NULL SELECT id, SUM(CASE WHEN month='Jan' THEN revenue END) AS Jan, ... (Feb to Dec) GROUP BY id Rows --> Cols FINAL RESULT id Jan Feb Mar 1 8000 7000 6000 2 9000 12000 NULL 3 NULL 10000 NULL PIVOT OK Each dept = 1 row Each month = 1 col Jan Feb Mar ... Jan to Dec columns with revenues Key Insight: Use CASE WHEN inside SUM() to conditionally aggregate revenue for each month. GROUP BY id collapses multiple rows per department into a single row with 12 month columns. Missing months automatically return NULL because SUM of no matching rows is NULL. TutorialsPoint - Reformat Department Table | Optimal Solution - Conditional Aggregation

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