[SQL 문제 풀이] Reformat Department Table (부서 테이블 다시 포맷)

May 15, 2025

[SQL 문제 풀이] Reformat Department Table (부서 테이블 다시 포맷)

Contents

Can you solve this real interview question? Reformat Department Table - 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).

https://leetcode.com/problems/reformat-department-table/description/


매달 부서 ID 열과 수익 열이 있도록 표를 다시 포맷하세요.

결과 테이블을 순서에 상관없이 반환하세요.

내가 작성한 쿼리

MySQL, Oracle


SELECT
    id,
    MAX(CASE WHEN month = 'Jan' THEN revenue END) Jan_Revenue,
    MAX(CASE WHEN month = 'Feb' THEN revenue END) Feb_Revenue,
    MAX(CASE WHEN month = 'Mar' THEN revenue END) Mar_Revenue,
    MAX(CASE WHEN month = 'Apr' THEN revenue END) Apr_Revenue,
    MAX(CASE WHEN month = 'May' THEN revenue END) May_Revenue,
    MAX(CASE WHEN month = 'Jun' THEN revenue END) Jun_Revenue,
    MAX(CASE WHEN month = 'Jul' THEN revenue END) Jul_Revenue,
    MAX(CASE WHEN month = 'Aug' THEN revenue END) Aug_Revenue,
    MAX(CASE WHEN month = 'Sep' THEN revenue END) Sep_Revenue,
    MAX(CASE WHEN month = 'Oct' THEN revenue END) Oct_Revenue,
    MAX(CASE WHEN month = 'Nov' THEN revenue END) Nov_Revenue,
    MAX(CASE WHEN month = 'Dec' THEN revenue END) Dec_Revenue
FROM Department
GROUP BY id;

Oracle


SELECT * 
FROM department 
PIVOT
    (
        MIN(revenue) revenue FOR month IN 
        (
            'Jan' Jan, 
            'Feb' Feb,
            'Mar' Mar,
            'Apr' Apr,
            'May' May,
            'Jun' Jun,
            'Jul' Jul,
            'Aug' Aug,
            'Sep' Sep,
            'Oct' Oct,
            'Nov' Nov,
            'Dec' Dec
        )
    )
ORDER BY id;

차이점

Oracle은 행을 열로 변환하는 함수 존재