[SQL] 피벗(Pivot) 테이블 완벽 가이드: Oracle, MySQL, PostgreSQL별 구현 패턴

[SQL] 피벗(Pivot) 테이블 완벽 가이드: 행 데이터를 열로 변환하는 DBMS별 패턴

데이터베이스 실무에서 가장 빈번한 요청 중 하나는 세로로 쌓인 로그 데이터를 가로 형태의 통계 리포트로 변환하는 것입니다. 이를 피벗(Pivot)이라 부르며, 사용 중인 DBMS에 따라 효율적인 구현 방식이 조금씩 다릅니다.

오늘은 CASE WHEN을 활용한 표준 방식부터 Oracle, MySQL, PostgreSQL 각각의 특화 문법까지 단계별로 상세히 알아보겠습니다.

---

1. [공통] 표준 SQL 패턴: CASE WHEN + GROUP BY

이 방식은 특정 DBMS에 종속되지 않는 '치트키'와 같습니다. MySQL, MariaDB, SQL Server, PostgreSQL 등 모든 환경에서 동일하게 작동하며 논리가 매우 명확합니다.

✅ 실무 예제: 상품별/월별 매출 현황

[Raw Data: sales_logs]

| 상품명 | 판매월 | 판매액 |

| :--- | :--- | :--- |

| 냉장고 | 01 | 150 |

| 세탁기 | 01 | 100 |

| 냉장고 | 02 | 200 |

SQL

SELECT 
    product_name,
    SUM(CASE WHEN sale_month = '01' THEN sales_amount ELSE 0 END) AS "1월",
    SUM(CASE WHEN sale_month = '02' THEN sales_amount ELSE 0 END) AS "2월",
    SUM(CASE WHEN sale_month = '03' THEN sales_amount ELSE 0 END) AS "3월",
    SUM(sales_amount) AS "전체합계"
FROM sales_logs
GROUP BY product_name;

• 동작 원리: 각 행을 검사하여 해당 월에 맞는 데이터만 남기고(CASE), 이를 상품명으로 묶어(GROUP BY) 합산(SUM)합니다.

---

2. [Oracle] 전용 PIVOT 함수 활용하기

오라클(11g 이상)은 PIVOT이라는 전용 연산자를 제공합니다. CASE WHEN을 여러 번 쓸 필요가 없어 쿼리가 비약적으로 짧아집니다.

✅ Oracle 특화 예제

SQL

SELECT *
FROM (
    -- 1. 피벗에 필요한 컬럼만 추출
    SELECT product_name, sale_month, sales_amount FROM sales_logs
)
PIVOT (
    -- 2. 집계 함수와 열로 변환할 대상 지정
    SUM(sales_amount) 
    FOR sale_month IN ('01' AS "1월", '02' AS "2월", '03' AS "3월")
)
ORDER BY product_name;

• 장점: 문법이 선언적이라 가독성이 매우 좋습니다.
• 주의: IN 절에 들어가는 값은 리터럴(상수)이어야 하며, 동적으로 늘릴 수는 없습니다.

---

3. [MySQL] 효율적인 피벗 구현 (MySQL 8.0+)

MySQL은 오라클과 같은 PIVOT 함수는 없지만, GROUP_CONCAT이나 윈도우 함수를 결합해 더 정교한 가공이 가능합니다.

✅ MySQL 특화 예제 (비중 포함)

SQL

SELECT 
    product_name,
    SUM(CASE WHEN sale_month = '01' THEN sales_amount ELSE 0 END) AS "1월",
    SUM(CASE WHEN sale_month = '02' THEN sales_amount ELSE 0 END) AS "2월",
    -- 윈도우 함수를 활용한 전체 대비 점유율 계산
    ROUND(SUM(sales_amount) / SUM(SUM(sales_amount)) OVER() * 100, 1) AS "점유율(%)"
FROM sales_logs
GROUP BY product_name;

• 팁: MySQL 8.0부터는 윈도우 함수(OVER)를 지원하므로, 피벗된 결과 옆에 전체 합계 대비 비중을 바로 붙여 리포트의 질을 높일 수 있습니다.

---

4. [PostgreSQL] tablefunc 모듈의 crosstab 활용

PostgreSQL에서는 tablefunc라는 강력한 확장 모듈을 통해 피벗을 수행할 수 있습니다.

✅ PostgreSQL 특화 예제

SQL

-- 1. 확장 모듈 설치 (최초 1회)
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 2. crosstab 함수 사용
SELECT * FROM crosstab(
  'SELECT product_name, sale_month, sales_amount FROM sales_logs ORDER BY 1, 2',
  'SELECT DISTINCT sale_month FROM sales_logs ORDER BY 1'
) AS result(product_name TEXT, "1월" INT, "2월" INT, "3월" INT);

• 장점: 복잡한 행-열 변환 연산에서 표준 CASE WHEN보다 처리 속도가 빠를 수 있습니다.
• 단점: 결과 스키마(열 이름과 타입)를 명시적으로 적어줘야 한다는 번거로움이 있습니다.

---

5. DBMS별 피벗 전략 비교 요약

구분	Oracle	MySQL / MariaDB	PostgreSQL
추천 방식	PIVOT 전용 함수	CASE WHEN + 집계	CASE WHEN 또는 crosstab
가독성	매우 높음	보통	보통
유연성	낮음 (값 고정 필요)	높음	높음
성능	최적화됨	데이터 양에 따라 다름	모듈 사용 시 효율적

---

💡 실무 최적화 팁: "어떤 방식을 쓸까요?"

1. 유지보수가 중요하다면: 무조건 표준 패턴(CASE WHEN)을 쓰세요. 나중에 DB를 마이그레이션하거나 다른 개발자가 쿼리를 볼 때 가장 이해하기 쉽습니다.
2. 데이터가 수천만 건이라면: DB에서 피벗 연산을 수행하는 것은 부담이 큽니다. SQL에서는 그룹화(GROUP BY)까지만 수행하고, 실제 가로 변환은 프론트엔드 라이브러리나 BI 툴(Tableau, PowerBI)에 맡기는 것이 아키텍처 측면에서 정답입니다.
3. 동적 열 생성: "판매 월이 매달 늘어나는데 자동으로 열이 추가되게 할 수 없나요?"라는 질문에 대한 답변은 "일반 SQL로는 불가능하다"입니다. 이 경우 어플리케이션 단(Java, Python 등)에서 쿼리를 동적으로 생성하는 로직이 필요합니다.