Watch 10 video solutions for Reformat Department Table, a easy level problem involving Database. This walkthrough by Frederik Müller has 7,893 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
Example 1:
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).
Problem Overview: The table stores department revenue in a row-based format: each row contains id, month, and revenue. The task is to reformat it into a pivoted table where each department id appears once and revenue for each month (Jan to Dec) becomes a separate column.
Approach 1: Pivot Table Approach (O(n) time, O(1) space)
This method reshapes rows into columns using a pivot operation. You iterate through all rows and map each month value to its corresponding column in the output row for that department. In SQL systems that support PIVOT, the database engine performs this transformation directly. In application code (Python or JavaScript), you typically maintain a dictionary keyed by id and assign revenue to the correct month field as you scan the records. The key idea is that each row updates exactly one month column for a department.
Approach 2: Data Aggregation with Conditional Logic (O(n) time, O(1) space)
This approach uses conditional aggregation to construct month columns. While scanning the table, each month column is computed with an expression like SUM(CASE WHEN month = 'Jan' THEN revenue END). The database groups rows by id and aggregates revenue into the correct column. The technique relies on GROUP BY plus conditional checks, which works in nearly every SQL engine. Implementations in languages like Java or C++ mimic the same idea by grouping rows by department id and updating month-specific fields during aggregation.
The core insight across both approaches: each record contributes revenue to exactly one month column. Because the dataset is scanned once and month mapping is constant-time, the transformation runs in linear time.
Recommended for interviews: The conditional aggregation solution is the most common answer. Interviewers expect you to group by department and use expressions such as CASE WHEN to pivot rows into columns. Understanding pivot-style transformations is common in database and SQL problems, especially those involving data aggregation and reporting queries.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Pivot Table Approach | O(n) | O(1) | When the database or framework supports pivot operations or when reshaping data programmatically in Python/JavaScript. |
| Conditional Aggregation (CASE + GROUP BY) | O(n) | O(1) | Best general solution. Works in nearly every SQL engine and is the standard interview answer. |
