PIVOT 과 UNPIVOT

행을 열로 돌립니다

2.2 에서 게시판과 깊이로 묶었더니 일곱 줄이 나왔습니다. 사람이 읽는 표로는 불편합니다. 게시판마다 한 줄이고 깊이가 가로로 놓이는 편이 낫습니다.

2.8 에서 조건부 집계로 그 모양을 만들었습니다. 같은 일을 하는 전용 문법이 PIVOT 입니다.

SELECT board_num,
       SUM(CASE WHEN depth = 0 THEN 1 ELSE 0 END) AS [0],
       SUM(CASE WHEN depth = 1 THEN 1 ELSE 0 END) AS [1],
       SUM(CASE WHEN depth = 2 THEN 1 ELSE 0 END) AS [2]
FROM Board.POSTS
GROUP BY board_num;

SELECT board_num, [0] AS 원글, [1] AS 답글, [2] AS 답답글
FROM (
    SELECT board_num, depth, num FROM Board.POSTS
) AS S
PIVOT (
    COUNT(num) FOR depth IN ([0], [1], [2])
) AS P
ORDER BY board_num;

결과가 같습니다. 표기만 다를 뿐입니다.

세 부분으로 이루어집니다

안쪽에서 열을 골라 두는 것이 중요합니다. PIVOT 은 "셈할 열" 과 "펼칠 열" 을 뺀 나머지 전부를 묶는 기준으로 삼습니다. 위에서 board_num 만 남긴 까닭입니다.

Board.POSTS 를 그대로 넣었다면 title · reg_date 까지 기준이 되어 글마다 한 줄이 나왔을 것입니다. 오류가 아니라 뜻이 달라지는 것이라 더 위험합니다.

값을 빠뜨리면 조용히 사라집니다

이 단원에서 가장 중요한 이야기입니다. IN 목록에 적은 값만 결과에 나옵니다. 빠뜨린 값의 자료는 오류 없이 없어집니다.

-- [2] 를 빠뜨렸습니다
SELECT SUM([0] + [1]) AS 합계
FROM (SELECT board_num, depth, num FROM Board.POSTS) AS S
PIVOT (COUNT(num) FOR depth IN ([0], [1])) AS P;

-- 모두 적었습니다
SELECT SUM([0] + [1] + [2]) AS 합계
FROM (SELECT board_num, depth, num FROM Board.POSTS) AS S
PIVOT (COUNT(num) FOR depth IN ([0], [1], [2])) AS P;

52건이 사라졌습니다. 오류도 경고도 없습니다. 통계 화면의 합이 실제와 맞지 않는 일이 이 자리에서 생깁니다.

반대로 없는 값을 적는 것은 안전합니다. [3] 을 추가하면 그 열이 모두 0 으로 나올 뿐입니다. 의심스러우면 넉넉히 적으십시오.

합계를 따로 세어 맞춰 보는 것이 가장 확실합니다. COUNT(*) 로 전체를 세어 PIVOT 결과의 합과 비교하면 빠진 값이 있는지 알 수 있습니다.

실제로 사용하는 형태

자유게시판의 말머리별 글 수를 가로로 펼쳐 봅니다. 조인으로 이름을 가져와 열 이름이 한글로 나오게 했습니다.

SELECT 게시판, [잡담], [후기], [정보], [질문], [모집]
FROM (
    SELECT B.name AS 게시판, C.name AS 말머리, P.num
    FROM Board.POSTS AS P
        INNER JOIN Board.BOARDS AS B ON B.num = P.board_num
        INNER JOIN Board.CATEGORIES AS C ON C.num = P.category_num
    WHERE P.board_num = 2
) AS S
PIVOT (
    COUNT(num) FOR 말머리 IN ([잡담], [후기], [정보], [질문], [모집])
) AS P;

안쪽에서 게시판 · 말머리 · num 셋만 남겼으므로 게시판이 묶는 기준이 되어 한 줄입니다.

반대로 — UNPIVOT

가로로 펼쳐진 것을 세로로 되돌립니다. 남이 만든 표를 받아 집계하기 좋은 모양으로 바꿀 때 사용합니다.

SELECT 게시판, 말머리, 건수
FROM (
    SELECT N'자유게시판' AS 게시판,
           45 AS 잡담, 42 AS 후기, 42 AS 정보,
           42 AS 질문, 39 AS 모집
) AS S
UNPIVOT (
    건수 FOR 말머리 IN ([잡담], [후기], [정보], [질문], [모집])
) AS U;

열 이름이 말머리 열의 값이 되고, 각 칸의 수가 건수 가 됐습니다.

UNPIVOT 은 NULL 인 칸을 버립니다. 빈칸도 남겨야 한다면 CROSS APPLY 나 UNION ALL(2.6)로 직접 펼쳐야 합니다.

가장 큰 제약 — 열 이름을 미리 알아야 합니다

IN 목록은 문장을 적을 때 정해져 있어야 합니다. 조회 결과로 채울 수 없습니다.

-- 이렇게는 안 됩니다
PIVOT (COUNT(num) FOR 말머리 IN (SELECT name FROM Board.CATEGORIES))

말머리가 늘어나면 문장을 찾아 고쳐야 합니다. 관리자가 화면에서 말머리를 추가할 수 있는 서비스라면 이 방식은 곧 어긋납니다.

해결책은 둘입니다.

• 세로로 내고 화면에서 돌립니다. 2.2 처럼 GROUP BY 로 낸 뒤 응용 프로그램이 표를 만듭니다. 대개 이쪽이 낫습니다 — 말머리가 늘어도 SQL 을 고칠 일이 없습니다.
• 문장을 문자열로 만들어 실행합니다(동적 SQL). 열 목록을 조회해 IN 자리에 끼워 넣는 방식인데, 문자열을 이어 붙이는 일이라 SQL 주입에 조심해야 합니다. 4.9 에서 다룹니다.

PIVOT 은 표준 SQL 이 아닙니다. 다른 데이터베이스로 옮길 일이 있다면 2.8 의 조건부 집계로 적어 두는 편이 안전합니다. 그쪽은 어디서나 동작합니다.

직접 해보기