1179. Reformat Department Table - Solution

The problem

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

Approach overview

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 1: Pivot Table Approach

This approach involves using a pivot table strategy to reformat the data. The key is to transform row data of each month's revenue into separate columns for each month. We will iterate through the table, grouping revenues by department id and arranging them into columns corresponding to each month.

Python

import pandas as pd

def reformat_department_table(df):
    # Pivot the table with id as index and month as columns
    pivot_df = df.pivot(index='id', columns='month', values='revenue')
    # Rename columns to include '_Revenue'
    pivot_df.columns = [f'{month}_Revenue' for month in pivot_df.columns]
    return pivot_df.reset_index().to_dict(orient='records')

JavaScript

function reformatDepartmentTable(department) {
    const result = new Map();
    const months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];

    department.forEach(({ id, revenue, month }) => {
        if (!result.has(id)) {
            result.set(id, { id: id });
        }
        result.get(id)[`${month}_Revenue`] = revenue;
    });

    return Array.from(result.values()).map(record => {
        months.forEach(month => {
            if (!record.hasOwnProperty(`${month}_Revenue`)) {
                record[`${month}_Revenue`] = null;
            }
        });
        return record;
    });
}

This solution leverages pandas to pivot the DataFrame. The pivot operation uses the 'id' as index and 'month' as columns, transforming the revenue data into month-column format. Finally, the columns are renamed to include the '_Revenue' suffix, and the result is returned in dictionary format.

Complexity

Time Complexity: O(n), where n is the number of rows in the input table.
Space Complexity: O(n), for storing the pivoted DataFrame.

Try this approach in the editor

Approach 2: Data Aggregation Approach

This approach focuses on iterating over the data, storing information using an aggregation map or dictionary. Each department will have its own dictionary that maps months to revenue, allowing us to easily access and format this data into the desired output structure.

Java

import java.util.*;

class DepartmentReformatter {
    public static List<Map<String, Integer>> reformat(List<Map<String, Object>> department) {
        Map<Integer, Map<String, Integer>> resultMap = new HashMap<>();
        String[] months = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
        
        for (Map<String, Object> row : department) {
            int id = (Integer) row.get("id");
            int revenue = (Integer) row.get("revenue");
            String month = (String) row.get("month");

            resultMap.putIfAbsent(id, new HashMap<>());
            resultMap.get(id).put(month + "_Revenue", revenue);
        }

        List<Map<String, Integer>> reformattedData = new ArrayList<>();
        for (int id : resultMap.keySet()) {
            Map<String, Integer> data = new HashMap<>();
            data.put("id", id);
            for (String month : months) {
                data.put(month + "_Revenue", resultMap.get(id).getOrDefault(month + "_Revenue", null));
            }
            reformattedData.add(data);
        }

        return reformattedData;
    }
}

C++

#include <vector>
#include <map>
#include <string>
using namespace std;

vector<map<string, int>> reformatDepartmentTable(vector<map<string, int>> department) {
    map<int, map<string, int>> resultMap;
    vector<string> months = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
    
    // Aggregate by id and fill revenue for each month
    for (auto &row : department) {
        int id = row["id"];
        int revenue = row["revenue"];
        string month = row["month"];
        resultMap[id][month + "_Revenue"] = revenue;
    }
    
    // Construct the result
    vector<map<string, int>> reformattedData;
    for (auto &[id, revenues] : resultMap) {
        map<string, int> data;
        data["id"] = id;
        for (const string &month : months) {
            data[month + "_Revenue"] = revenues[month + "_Revenue"];
        }
        reformattedData.push_back(data);
    }
    return reformattedData;
}

This Java solution uses a HashMap to collect month-specific revenue data for each department. After populating this map, it constructs the output list by iterating over departments, ensuring that all months are represented, inserting null where revenue data is absent.

Complexity

Time Complexity: O(n * m), where n is the number of departments and m is the number of months.
Space Complexity: O(n * m), with m fixed at 12, for storing department-month mappings.

Try this approach in the editor

Approach 3: Default Approach

MySQL

# Write your MySQL query statement below
SELECT
    id,
    SUM(
        CASE month
            WHEN 'Jan' THEN revenue
        END
    ) AS Jan_Revenue,
    SUM(
        CASE month
            WHEN 'Feb' THEN revenue
        END
    ) AS Feb_Revenue,
    SUM(
        CASE month
            WHEN 'Mar' THEN revenue
        END
    ) AS Mar_Revenue,
    SUM(
        CASE month
            WHEN 'Apr' THEN revenue
        END
    ) AS Apr_Revenue,
    SUM(
        CASE month
            WHEN 'May' THEN revenue
        END
    ) AS May_Revenue,
    SUM(
        CASE month
            WHEN 'Jun' THEN revenue
        END
    ) AS Jun_Revenue,
    SUM(
        CASE month
            WHEN 'Jul' THEN revenue
        END
    ) AS Jul_Revenue,
    SUM(
        CASE month
            WHEN 'Aug' THEN revenue
        END
    ) AS Aug_Revenue,
    SUM(
        CASE month
            WHEN 'Sep' THEN revenue
        END
    ) AS Sep_Revenue,
    SUM(
        CASE month
            WHEN 'Oct' THEN revenue
        END
    ) AS Oct_Revenue,
    SUM(
        CASE month
            WHEN 'Nov' THEN revenue
        END
    ) AS Nov_Revenue,
    SUM(
        CASE month
            WHEN 'Dec' THEN revenue
        END
    ) AS Dec_Revenue
FROM Department
GROUP BY 1;

Try this approach in the editor

Complexity comparison

Approach	Complexity
Pivot Table Approach	Time Complexity: O(n), where n is the number of rows in the input table. Space Complexity: O(n), for storing the pivoted DataFrame.
Data Aggregation Approach	Time Complexity: O(n * m), where n is the number of departments and m is the number of months. Space Complexity: O(n * m), with m fixed at 12, for storing department-month mappings.
Default Approach	–

Complexity analysis

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.

Frequently asked questions

Is Reformat Department Table easy or hard?

Reformat Department Table is classified as an Easy database problem. The main concept is converting row-based data into column format using pivot logic or conditional aggregation.

Reformat Department Table Python/Java solution

Python and Java implementations usually simulate the pivot with a dictionary or map keyed by department id. While iterating through rows, the algorithm assigns revenue to the correct month field (Jan–Dec). The overall complexity remains O(n) time with constant extra space.

How to solve Reformat Department Table in O(n)?

Scan the Department table once and group rows by id. Use conditional aggregation such as SUM(CASE WHEN month = 'Feb' THEN revenue END) for each month column. Since each row contributes to exactly one column and grouping is done in a single pass, the total runtime is linear.

What is the best approach for Reformat Department Table?

Conditional aggregation using GROUP BY and CASE statements is the most reliable approach. Each month column is created with expressions like SUM(CASE WHEN month = 'Jan' THEN revenue END). This scans the table once and groups rows by department id, giving O(n) time complexity.

Is Reformat Department Table asked at Google/Amazon/Meta?

Row-to-column pivot and aggregation problems frequently appear in SQL interview rounds at companies like Amazon, Google, and Meta. Variations of this question test knowledge of GROUP BY, CASE expressions, and reporting-style queries.

What data structure is used in Reformat Department Table?

In SQL, the solution relies on relational aggregation using GROUP BY and conditional expressions. In programmatic implementations, a hash map keyed by department id is often used to collect revenue values for each month column.

What is the time complexity of Reformat Department Table?

The query runs in O(n) time where n is the number of rows in the Department table. Each row is processed once during aggregation or pivoting. Space complexity is O(1) because the number of output columns (Jan–Dec) is constant.

Reformat Department Table solution and explanation

The problem

Approach overview

Approach 1: Pivot Table Approach

Approach 2: Data Aggregation Approach

Approach 3: Default Approach

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Reformat Department Table solution and explanation

The problem

Approach overview

Approach 1: Pivot Table Approach

Approach 2: Data Aggregation Approach

Approach 3: Default Approach

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Related problems