Reformat Department Table

Reformat Department Table

What is this problem about?

The Reformat Department Table SQL problem asks you to pivot a long-format revenue-by-month table into a wide format: each row is a department with separate columns for each month's revenue. This easy SQL database problem tests conditional aggregation pivoting. The database interview pattern is demonstrated.

Why is this asked in interviews?

Microsoft, Meta, Amazon, and Google ask this as a fundamental SQL pivot problem — transforming rows into columns. It validates the CASE WHEN inside MAX/SUM aggregation pattern.

Algorithmic pattern used

Conditional aggregation pivot.

SELECT id,
  MAX(CASE WHEN month='Jan' THEN revenue END) AS Jan_Revenue,
  MAX(CASE WHEN month='Feb' THEN revenue END) AS Feb_Revenue,
  -- ... for all 12 months
  MAX(CASE WHEN month='Dec' THEN revenue END) AS Dec_Revenue
FROM Department
GROUP BY id

Example explanation

Department: (1,'Jan',8000),(1,'Mar',10000),(2,'Jan',9000),(2,'Jun',12000). Pivoted: dept1 → Jan=8000,Mar=10000,others=NULL. dept2 → Jan=9000,Jun=12000,others=NULL.

Common mistakes candidates make

• Using SUM instead of MAX (both work for non-null months but MAX is conventional here).
• Missing some months in the CASE WHEN list.
• Not using GROUP BY on department id.
• Using WHERE instead of CASE WHEN for column filtering.

Interview preparation tip

SQL pivot problems always use MAX(CASE WHEN category='X' THEN value END) AS X. Build all category columns in the SELECT. GROUP BY the row identifier. Practice with different categories: "monthly revenue," "product category scores," "weekly metrics." The CASE WHEN aggregation pattern is the universal SQL pivot technique across all SQL dialects.