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

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

Rating: 1 out of 5.

Posted in SQL