피벗 테이블은 행을 열로 바꿔요. 그룹마다 한 행이 되고, 분기별로 열을 하나씩 두는 것처럼 다른 필드의 값마다 열이 하나씩 생겨요. SQL에서는 SUM 같은 집계 함수 안에 CASE를 넣고, 각 행의 기준이 될 값으로 그룹화해서 만들어요. 예제는 PostgreSQL, MySQL, SQLite, SQL Server에서 모두 동작해요.
SQL에서 피벗 테이블 만드는 방법
출력 열마다 집계 함수를 하나씩 작성하세요. 그 안의 CASE가 해당 열에 속한 행만 통과시키고, GROUP BY가 그룹마다 한 행을 만들어요:
SELECT
row_column,
SUM(CASE WHEN pivot_column = 'value 1' THEN amount ELSE 0 END) AS value_1,
SUM(CASE WHEN pivot_column = 'value 2' THEN amount ELSE 0 END) AS value_2
FROM table_name
GROUP BY row_column;
여기서는 제품마다 한 행이 생기고, 분기별 판매 수량이 나란히 표시돼요:
-- Input: sales
-- product | quarter | units
-- ---------+---------+------
-- Keyboard | Q1 | 120
-- Keyboard | Q2 | 95
-- Monitor | Q1 | 40
-- Monitor | Q2 | 55
-- Mouse | Q2 | 210
SELECT
product,
SUM(CASE WHEN quarter = 'Q1' THEN units ELSE 0 END) AS q1,
SUM(CASE WHEN quarter = 'Q2' THEN units ELSE 0 END) AS q2
FROM sales
GROUP BY product
ORDER BY product;
-- Output:
-- product | q1 | q2
-- ---------+-----+----
-- Keyboard | 120 | 95
-- Monitor | 40 | 55
-- Mouse | 0 | 210
알아 둘 규칙
-
모든 열의 이름은 직접 지정해야 해요. SQL은 데이터로부터 열을 만들어 낼 수 없으므로, 세 번째 분기가 있으면 세 번째
CASE줄이 필요해요. 값 목록이 계속 바뀐다면 애플리케이션 코드에서 쿼리를 생성하세요. -
ELSE 0은SUM과 개수 세기에만 쓰세요. 없는 조합을 NULL 대신 0으로 바꿔 줘요.AVG,MIN,MAX를 쓸 때는ELSE를 빼세요. 그렇지 않으면 0이 실제 값으로 계산돼요:
-- Input: the sales table above
SELECT
product,
ROUND(AVG(CASE WHEN quarter = 'Q1' THEN units ELSE 0 END), 1) AS q1_else_0,
ROUND(AVG(CASE WHEN quarter = 'Q1' THEN units END), 1) AS q1_no_else
FROM sales
GROUP BY product
ORDER BY product;
-- Output:
-- product | q1_else_0 | q1_no_else
-- ---------+-----------+-----------
-- Keyboard | 60.0 | 120.0
-- Monitor | 20.0 | 40.0
-- Mouse | 0.0 | NULL
다른 문법
PostgreSQL과 SQLite의 FILTER, SQL Server의 PIVOT 연산자도 자주 보게 될 거예요. 둘 다 없는 조합을 0이 아닌 NULL로 남겨요:
-- Input: the sales table above
-- PostgreSQL and SQLite
SELECT
product,
SUM(units) FILTER (WHERE quarter = 'Q1') AS q1,
SUM(units) FILTER (WHERE quarter = 'Q2') AS q2
FROM sales
GROUP BY product
ORDER BY product;
-- SQL Server
SELECT product, [Q1] AS q1, [Q2] AS q2
FROM (SELECT product, quarter, units FROM sales) AS src
PIVOT (SUM(units) FOR quarter IN ([Q1], [Q2])) AS p
ORDER BY product;
-- Output:
-- product | q1 | q2
-- ---------+------+----
-- Keyboard | 120 | 95
-- Monitor | 40 | 55
-- Mouse | NULL | 210
MySQL 코드에서는 CASE 버전을 짧게 쓴 SUM(IF(quarter = 'Q1', units, 0))를 자주 볼 수 있어요. PostgreSQL의 tablefunc 확장에도 crosstab() 함수가 있어요. PostgreSQL에서 crosstab()으로 피벗 테이블 만들기를 참고하세요.
피벗 테이블은 언제 사용해야 할까요?
행 목록을 표 형태로 보는 게 더 읽기 쉬울 때 피벗하세요. 대표적인 경우는 다음과 같아요.
하나의 지표를 나란히 비교하기
비교하려는 값(요일, 분기, 지역)을 긴 목록에 흩어 두지 않고 한 행에 나란히 배치하세요.
열마다 여러 지표 표시하기
출력 열은 각각 독립된 집계이므로 짝지어 쓸 수 있어요. 기기별 세션 수와 페이지뷰, 채널별 주문 수와 매출처럼요.
카테고리별 행 개수 세기
합계 대신 개수를 세려면 일치하는 행마다 1을 더하거나 COUNT(CASE WHEN ... THEN 1 END)를 사용하세요. COUNT는 다른 행에서 생기는 NULL을 건너뛰어요. 상태별 작업 수나 우선순위별 티켓 수를 이렇게 구해요.
키-값 행을 열로 바꾸기
설정이나 제품 속성은 이름과 값마다 한 행씩 저장되는 경우가 많아요. MAX(CASE ...)로 피벗하면 항목마다 한 행, 속성마다 한 열이 생겨요. MAX는 텍스트에도 동작하며 각 그룹의 유일한 값을 골라요.
SQL 피벗 테이블 실전 예제
플랫폼별 평균 앱 평점
평균을 낼 때는 ELSE를 빼세요. 그래야 특정 플랫폼에 리뷰가 없는 앱이 잘못된 점수 대신 NULL로 표시돼요:
-- Input: reviews
-- app | platform | rating
-- ---------+----------+-------
-- Budgetly | iOS | 5
-- Budgetly | iOS | 4
-- Budgetly | Android | 3
-- FitTrack | Android | 4
-- FitTrack | Android | 5
-- In SQL Server, AVG of an integer column drops the decimals: use rating * 1.0
SELECT
app,
ROUND(AVG(CASE WHEN platform = 'iOS' THEN rating END), 1) AS ios,
ROUND(AVG(CASE WHEN platform = 'Android' THEN rating END), 1) AS android
FROM reviews
GROUP BY app
ORDER BY app;
-- Output:
-- app | ios | android
-- ---------+------+--------
-- Budgetly | 4.5 | 3.0
-- FitTrack | NULL | 4.5
지역별·채널별 주문 수와 매출
채널마다 주문 수와 합계, 두 가지 지표를 보여 줘요:
-- Input: orders
-- id | region | channel | amount
-- ---+--------+---------+-------
-- 1 | North | web | 120.00
-- 2 | North | store | 80.00
-- 3 | North | web | 45.50
-- 4 | South | store | 210.00
-- 5 | South | store | 64.00
SELECT
region,
COUNT(CASE WHEN channel = 'web' THEN 1 END) AS web_orders,
SUM(CASE WHEN channel = 'web' THEN amount ELSE 0 END) AS web_sales,
COUNT(CASE WHEN channel = 'store' THEN 1 END) AS store_orders,
SUM(CASE WHEN channel = 'store' THEN amount ELSE 0 END) AS store_sales
FROM orders
GROUP BY region
ORDER BY region;
-- Output:
-- region | web_orders | web_sales | store_orders | store_sales
-- -------+------------+-----------+--------------+------------
-- North | 2 | 165.50 | 1 | 80.00
-- South | 0 | 0 | 2 | 274.00
상담원별·우선순위별 지원 티켓 수
우선순위별 개수와 전체 합계예요. COUNT(*)는 여전히 그룹의 모든 행을 세요:
-- Input: tickets
-- id | agent | priority
-- ---+-------+---------
-- 1 | Priya | urgent
-- 2 | Priya | normal
-- 3 | Marco | normal
-- 4 | Priya | normal
-- 5 | Marco | low
SELECT
agent,
COUNT(CASE WHEN priority = 'low' THEN 1 END) AS low,
COUNT(CASE WHEN priority = 'normal' THEN 1 END) AS normal,
COUNT(CASE WHEN priority = 'urgent' THEN 1 END) AS urgent,
COUNT(*) AS total
FROM tickets
GROUP BY agent
ORDER BY agent;
-- Output:
-- agent | low | normal | urgent | total
-- ------+-----+--------+--------+------
-- Marco | 1 | 1 | 0 | 2
-- Priya | 0 | 2 | 1 | 3
사용자당 설정 한 행
각 설정은 이름과 값으로 저장돼요. 설정을 지정한 적 없는 사용자는 해당 열에 NULL이 표시돼요:
-- Input: user_settings
-- user_id | setting | value
-- --------+----------+--------------
-- 1 | theme | dark
-- 1 | language | en
-- 2 | language | de
-- 2 | timezone | Europe/Berlin
-- 3 | theme | light
SELECT
user_id,
MAX(CASE WHEN setting = 'theme' THEN value END) AS theme,
MAX(CASE WHEN setting = 'language' THEN value END) AS language,
MAX(CASE WHEN setting = 'timezone' THEN value END) AS timezone
FROM user_settings
GROUP BY user_id
ORDER BY user_id;
-- Output:
-- user_id | theme | language | timezone
-- --------+-------+----------+--------------
-- 1 | dark | en | NULL
-- 2 | NULL | de | Europe/Berlin
-- 3 | light | NULL | NULL
피벗 테이블 관련 추가 자료
이 블로그의 글:
-
PostgreSQL에서 피벗하는 방법: PostgreSQL의
CASE와crosstab() -
PostgreSQL에서 crosstab()으로 피벗 테이블 만들기:
tablefunc확장 자세히 보기 - SQL CASE 문 가이드: 모든 피벗 열 안에 들어가는 조건
- SQL GROUP BY 가이드: 피벗하기 전에 행 그룹화하기
공식 문서:
- PostgreSQL: 집계 표현식과 FILTER 및 crosstab()
- MySQL: CASE 연산자
- SQLite: 집계 함수와 FILTER
- SQL Server: PIVOT과 UNPIVOT
Beekeeper Studio는 무료 오픈 소스 데이터베이스 GUI예요
지금까지 써 본 SQL 쿼리 & 에디터 도구 중 최고예요. 데이터베이스 관리에 필요한 모든 게 다 있어요. - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studio는 빠르고 직관적이며 사용하기 쉬워요. 다양한 데이터베이스를 지원하고 Windows, Mac, Linux에서 모두 잘 작동해요.
Beekeeper Studio 사용자 후기
"Beekeeper Studio가 예전 SQL 작업 방식을 완전히 대체했어요. 빠르고 직관적이라 데이터베이스 작업이 다시 즐거워졌어요."
"여러 데이터베이스 GUI를 써 봤지만, Beekeeper는 기능과 단순함 사이의 균형이 완벽해요. 그냥 잘 돼요."