SQL Interview Questions – Reformat Department Table

Problem Description –

Write an SQL query to 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 query result format is in the following example.

Difficulty Level – Easy

Problem Link – Department Table

Solution –

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 1
ORDER BY 1

To solve this problem, first we need to use CASE statements.

SELECT
    id,
    (CASE WHEN month = 'Jan' THEN revenue ELSE NULL END) as Jan_Revenue,
    (CASE WHEN month = 'Feb' THEN revenue ELSE NULL END) as Feb_Revenue,
    (CASE WHEN month = 'Mar' THEN revenue ELSE NULL END) as Mar_Revenue,
    (CASE WHEN month = 'Apr' THEN revenue ELSE NULL END) as Apr_Revenue,
    (CASE WHEN month = 'May' THEN revenue ELSE NULL END) as May_Revenue,
    (CASE WHEN month = 'jun' THEN revenue ELSE NULL END) as Jun_Revenue,
    (CASE WHEN month = 'Jul' THEN revenue ELSE NULL END) as Jul_Revenue,
    (CASE WHEN month = 'Aug' THEN revenue ELSE NULL END) as Aug_Revenue,
    (CASE WHEN month = 'Sep' THEN revenue ELSE NULL END) as Sep_Revenue,
    (CASE WHEN month = 'Oct' THEN revenue ELSE NULL END) as Oct_Revenue,
    (CASE WHEN month = 'Nov' THEN revenue ELSE NULL END) as Nov_Revenue,
    (CASE WHEN month = 'Dec' THEN revenue ELSE NULL END) as Dec_Revenue
FROM Department
ORDER BY 1

This step will give us almost the desired result that we want. All we need to do rest is use SUM function to format the table as it is given in the problem.