[MSSQL] PIVOT.. 으로 데이터 드러눕히기

PIVOT이란?

PIVOT은 테이블의 행(row) 데이터를 열(column)으로 변환해 가로형 요약 뷰를 만드는 SQL 문법이다.
엑셀의 피벗 테이블과 완전히 같은 개념이다. 세로로 쌓인 데이터를 가로로 펼쳐서 한눈에 비교하기 좋은 형태로 만들어준다.
반대로 열을 다시 행으로 돌리는 건 UNPIVOT이 담당한다.

 

 

PIVOT 변환 구조

클로드가 만들어준 테이블

month 컬럼의 각 값(2024-01, 02, 03)이 열 이름으로 변환되고, revenue 값이 각 셀을 채우는 구조이다.
이것이 PIVOT의 핵심 메커니즘이다.

 

 

PIVOT이 필요한 이유

게임/매출 데이터를 분석하다 보면 이런 상황이 자주 발생한다.

• 월별 매출이 세로로 쌓여 있는데, 가로로 펼쳐서 월별 비교를 해야 할 때
• 세그먼트별 항목 수치를 한 행에 나란히 놓고 보고서를 만들어야 할 때
• BI 대시보드나 보고용 쿼리에서 크로스탭 형태가 필요할 때

 

 

기본 문법 구조

SELECT *
FROM [테이블명] 또는 [서브쿼리]
PIVOT (
    집계함수(집계할_컬럼) FOR 열로_바꿀_컬럼 IN ([값1], [값2], [값3])
) AS pvt;

3가지 핵심 요소만 기억하면 된다.

• 집계함수(집계할 컬럼) : 각 셀에 들어갈 값. SUM, AVG, AOUNT, MAX 등을 사용한다.
• FOR 열로 바꿀 컬럼 : 행 값을 열 이름으로 바꿀 컬럼을 지정한다.
• IN ([값1], [값2], ...) : 새로 만들어질 열 이름 목록을 직접 명시한다.

기본 사용 예시

SELECT *
FROM (
    SELECT salesClass, month, revenue
    FROM sales_data
) AS src
PIVOT (
    SUM(revenue) FOR month IN ([2024-01], [2024-02], [2024-03])
) AS pvt;

결과:

salesClass	2024-01	2024-02	2024-03
A	100	150	200
B	80	120	90

• FOR month IN (...) 안에 들어가는 값은 실제 데이터에 존재하는 month 컬럼 값이다.
• 대괄호([ ])는 하이픈(-) 같은 특수문자가 포함된 컬럼명을 처리하기 위해 사용한다.

 

 

PIVOT 작성 시 핵심 규칙

1) 서브쿼리에 필요한 컬럼만 남겨야 한다.

PIVOT은 서브쿼리에 존재하는 컬럼 중 FOR 절과 집계 대상 컬럼을 제외한 나머지를 자동으로 GROUP BY 기준으로 삼는다.

-- ❌ 잘못된 예 : gameDataId가 있으면 salesClass + gameDataId 조합으로 그룹이 나뉨
SELECT *
FROM (
    SELECT salesClass, gameDataId, month, revenue  -- gameDataId가 의도치 않은 그룹 기준이 됨
    FROM sales_data
) AS src
PIVOT (
    SUM(revenue) FOR month IN ([2024-01], [2024-02], [2024-03])
) AS pvt;

-- ✅ 올바른 예 : 필요한 컬럼만 명시
SELECT *
FROM (
    SELECT salesClass, month, revenue
    FROM sales_data
) AS src
PIVOT (
    SUM(revenue) FOR month IN ([2024-01], [2024-02], [2024-03])
) AS pvt;

PIVOT 쿼리에서 가장 많이 실수하는 부분이다.
서브쿼리에 컬럼이 많으면 결과 행이 예상보다 많아지는 이유가 여기에 있다.

2) In 절의 값은 반드시 사전에 알고 있어야 한다.

PIVOT의 가장 큰 제약은 IN 절에 들어갈 열 값을 쿼리 작성 시점에 직접 명시해야 한다는 점이다.

-- 컬럼이 고정된 경우 : 정적 PIVOT으로 충분
FOR month IN ([2024-01], [2024-02], [2024-03])

-- 컬럼이 동적으로 바뀌는 경우 : 동적 PIVOT이 필요하다 (아래에서 설명)

동적 PIVOT (Dynamic PIVOT)

데이터에 따라 열 값이 달라지는 경우, 즉 어떤 값이 올지 미리 알 수 없는 경우에는 동적으로 SQL을 조립해 실행해야 한다.

DECLARE @cols NVARCHAR(MAX);
DECLARE @sql  NVARCHAR(MAX);

-- 1단계 : 열로 만들 값 목록을 동적으로 조회
SELECT @cols = STRING_AGG(QUOTENAME(month), ', ')
               WITHIN GROUP (ORDER BY month)
FROM (SELECT DISTINCT month FROM sales_data) AS t;

-- 2단계 : 동적 SQL 조립
SET @sql = N'
            SELECT *
            FROM (
                SELECT salesClass, month, revenue
                FROM sales_data
            ) AS src
            PIVOT (
                SUM(revenue) FOR month IN (' + @cols + N')
            ) AS pvt;';

-- 3단계 : 실행
EXEC sp_executesql @sql;

 

 

UNPIVOT : PIVOT의 반대

열(Column)을 다시 행(Row)로 변환할 때 사용한다.

SELECT salesClass, month, revenue
FROM (
    SELECT salesClass, [2024-01], [2024-02], [2024-03]
    FROM pivot_result
) AS src
UNPIVOT (
    revenue FOR month IN ([2024-01], [2024-02], [2024-03])
) AS unpvt;

• PIVOT으로 펼친 데이터를 다시 세로 형태로 되돌릴 때 활용한다.
• ETL 파이프라인에서 가로형 원본 데이터를 세로형으로 정규화할 때 유용하다.

 

 

PIVOT vs CASE WHEN 비교

PIVOT 이전에는 CASE WHEN으로 같은 결과를 만들었다.

-- CASE WHEN 방식
SELECT
    salesClass,
    SUM(CASE WHEN month = '2024-01' THEN revenue ELSE 0 END) AS [2024-01],
    SUM(CASE WHEN month = '2024-02' THEN revenue ELSE 0 END) AS [2024-02],
    SUM(CASE WHEN month = '2024-03' THEN revenue ELSE 0 END) AS [2024-03]
FROM sales_data
GROUP BY salesClass;

구분	PIVOT	CASE WHEN
가독성	열이 많을수록 깔끔	열이 많을수록 코드가 길어짐
유연성	IN 절 값 고정 필요	조건을 자유롭게 변형 가능
동적 열	동적 SQL 필요	동적 SQL 필요
성능	유사	유사
NULL 처리	NULL 그대로 반환	ELSE 0으로 제어 가능

• 열 수가 많고 고정되어 있다면 PIVOT이 더 깔끔하다.
• NULL을 0으로 바꾸는 등 세부 제어가 필요하면 CASE WHEN이 유리하다.

NULL 처리: PIVOT 결과의 NULL 채우기

PIVOT에서 해당 값이 없으면 자동으로 NULL이 들어간다.
이를 0이나 다른 값으로 채우려면 ISNULL 또는 COALSCE로 감싸야 한다.

SELECT
    salesClass,
    ISNULL([2024-01], 0) AS [2024-01],
    ISNULL([2024-02], 0) AS [2024-02],
    ISNULL([2024-03], 0) AS [2024-03]
FROM (
    SELECT salesClass, month, revenue
    FROM sales_data
) AS src
PIVOT (
    SUM(revenue) FOR month IN ([2024-01], [2024-02], [2024-03])
) AS pvt;

 

 

PIVOT 사용 시 주의사항

✔ 서브쿼리 컬럼을 최소화하자

• 집계 기준, FOR 대상, 집계 컬럼 딱 3가지만 남겨야 의도한 그룹핑이 된다.

✔ 동적 PIVOT은 SQL 인젝션을 주의하자

• QUOTENAME()을 반드시 사용해 컬럼명을 감싸줘야 특수 문자나 악의적인 입력을 방어할 수 있다.

✔ NULL을 반드시 처리하자

• 데이터가 없는 조합은 NULL로 채워진다. 집계 목적이라면 ISNULL(컬럼, 0)으로 처리하는 것이 일반적이다.

✔ IN 절 값의 순서도 결과 열 순서에 영향을 준다

• 동적 PIVOT에서 ORDER BY를 통해 열 순서를 제어하자.

 

 

결론 : 한 줄 요약

PIVOT은 세로로 쌓인 행 데이터를 가로로 펼쳐 크로스탭 형태의 요약 뷰를 만드는 MSSQL 문법으로, 서브쿼리 컬럼 설계와 동적 PIVOT 패턴만 익히면 보고서/대시보드용 쿼리를 훨씬 간결하게 작성할 수 있다.

 

 

References

https://learn.microsoft.com/ko-kr/sql/t-sql/queries/from-using-pivot-and-unpivot
https://learn.microsoft.com/ko-kr/sql/t-sql/functions/string-agg-transact-sql