Reformat Department Table

Difficulty: Easy | Acceptance: 76.30% | Paid: No Topics: Database

Table: Department

+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | revenue | int | | month | varchar | +-------------+--------- (id, month) is the primary key of this table. The table has information about the revenue of each department in each month. The month has values in [‘Jan’,‘Feb’,‘Mar’,‘Apr’,‘May’,‘Jun’,‘Jul’,‘Aug’,‘Sep’,‘Oct’,‘Nov’,‘Dec’].

Write an SQL query to reformat the table so that there is a department id column and a revenue column for each month.

The result table should have the following format:

+------+---------------+---------------+---------------+-----+---------------+ | id | Jan_Revenue | Feb_Revenue | Mar_Revenue | … | Dec_Revenue | +------+---------------+---------------+---------------+-----+---------------+ where Jan_Revenue is the revenue of the department in January, Feb_Revenue is the revenue of the department in February, etc.

• Examples
• Constraints
• Approach 1: Conditional Aggregation (CASE WHEN)
• Approach 2: MySQL IF Function

Examples

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 for the department with id 1 in January is 8000, in February is 7000, and in March is 6000. The revenue for the department with id 2 in January is 9000. The revenue for the department with id 3 in February is 10000. All other months have null values.

Constraints

The month values are limited to the first three letters of the English month names (e.g., 'Jan', 'Feb', etc.).

Approach 1: Conditional Aggregation (CASE WHEN)

Intuition We need to pivot the table by converting row values (months) into column names. This can be achieved by grouping the data by id and using conditional aggregation to sum the revenue for each specific month.

Steps

• Select the id column.
• For each month (Jan through Dec), use a CASE WHEN statement inside a SUM function. If the current row’s month matches the target month, include the revenue; otherwise, include NULL (which is ignored by SUM).
• Group the results by id.
• Order the results by id in ascending order.

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

Complexity

• Time: O(N), where N is the number of rows in the Department table. We scan the table once.
• Space: O(1), assuming the output size is not considered in space complexity (or O(M) for the result set where M is the number of unique IDs).
• Notes: This approach is standard SQL and works across almost all database systems (MySQL, PostgreSQL, SQL Server, Oracle).

Approach 2: MySQL IF Function

Intuition MySQL provides a shorthand IF(condition, true_value, false_value) function which can make the query slightly more concise than the standard CASE WHEN syntax, though the logic remains identical.

Steps

• Select the id column.
• For each month, use the IF function inside a SUM function. Check if the month column equals the target month string. If true, return revenue; otherwise, return NULL.
• Group by id and order by id.

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

Complexity

• Time: O(N), where N is the number of rows in the Department table.
• Space: O(1), excluding the result set storage.
• Notes: This syntax is specific to MySQL. For other databases like PostgreSQL or SQL Server, CASE WHEN is preferred for portability.