SQL Server PIVOT 절 완벽 가이드: 행을 열로 변환하는 방법

SQL Server(Transact-SQL)에서 PIVOT 절은 교차 탭(cross tabulation) 기능을 제공하여 한 테이블의 데이터를 다른 형태로 변환할 수 있게 해줍니다. 즉, 집계 결과를 얻어 행(row) 방향의 데이터를 열(column) 방향으로 회전시키는 역할을 합니다.

아래 예시는 합계를 계산한 후 데이터 테이블의 행을 열로 변환하는 과정을 보여줍니다.

PIVOT 절 문법

SELECT cot_dautien AS [bidanh_cot_dautien],
       [giatri_chuyen1], [giatri_chuyen2], ... [giatri_chuyen_n]
FROM
(
    SELECT bang_nguon
    FROM bang_nguon
) AS bidanh_bang_nguon
PIVOT
(
    ham_tong (cot_tong)
    FOR cot_chuyen
    IN ([giatri_chuyen1], [giatri_chuyen2], ... [giatri_chuyen_n])
) AS bidanh_bang_chuyen;

매개변수 및 값 설명

cot_dautien
전환 후 새 테이블에서 첫 번째 열이 될 열 또는 표현식입니다.

bidanh_cot_dautien
전환 후 새 테이블에 있는 첫 번째 열의 이름입니다.

giatri_chuyen1, giatri_chuyen2, ... giatri_chuyen_n
전환할 값들의 목록입니다.

bang_nguon
원본 데이터(초기 데이터)를 새 테이블로 가져오는 SELECT 문입니다.

bidanh_bang_nguon
bang_nguon의 별칭(alias)입니다.

ham_tong
SUM, COUNT, MIN, MAX 또는 AVG와 같은 집계 함수입니다.

cot_tong
ham_tong과 함께 사용되는 열 또는 표현식입니다.

cot_chuyen
전환할 값을 포함하고 있는 열입니다.

bidanh_bang_chuyen
전환 후 생성된 테이블의 별칭입니다.

튜토리얼의 단계를 따라 실습하려면 이 글 마지막에 있는 DDL(테이블 생성) 및 DML(데이터 생성) 섹션을 참고하여 자신의 데이터베이스에서 직접 실행해 보세요.

PIVOT 절 활용 예제

다음과 같은 데이터가 있는 테이블이 있다고 가정해 보겠습니다.

PIVOT 절을 사용하여 교차 쿼리를 만들려면 아래 SQL 명령을 실행합니다.

SELECT 'TongLuong' AS TongLuongTheoPhong,
       [30], [45]
FROM
(
    SELECT id_phong, luong
    FROM nhanvien
) AS BangNguon
PIVOT
(
    SUM(luong)
    FOR id_phong IN ([30], [45])
) AS BangChuyen;

실행 결과는 다음과 같습니다.

위 예제는 데이터가 전환된 후의 테이블을 생성하며, 부서 ID가 30인 부서와 45인 부서의 총 급여를 나타냅니다. 결과는 하나의 행에 2개의 열로 구성되며, 각 열이 하나의 부서를 의미합니다.

교차 쿼리의 새 테이블 열 지정하기

먼저 전환 테이블에 포함하고 싶은 정보 필드를 결정해야 합니다. 이 예제에서는 TongLuong이 첫 번째 열이 되고, 그 다음에 id_phong 30과 id_phong 45 두 개의 열이 옵니다.

SELECT 'TongLuong' AS TongLuongTheoPhong,
       [30], [45]

원본 테이블의 데이터 결정하기

다음은 새 테이블에 원본 데이터를 반환할 SELECT 문입니다. 이 예제에서는 nhanvien 테이블에서 id_phong과 luong을 가져옵니다.

(SELECT id_phong, luong
FROM nhanvien) AS BangNguon

원본 쿼리에는 반드시 별칭을 지정해야 하며, 이 예제에서는 BangNguon을 사용했습니다.

집계 함수 결정하기

교차 쿼리에 사용할 수 있는 함수에는 SUM, COUNT, MIN, MAX, AVG가 있습니다. 이 예제에서는 합계를 구하는 SUM 함수를 사용합니다.

PIVOT
(SUM(luong)

전환할 값 결정하기

마지막으로 결과에 포함시킬 전환 대상 값들을 정해야 합니다. 이 값들이 교차 쿼리의 열 머리글(column header)이 됩니다.

이 예제에서는 id_phong 30과 45만 반환하면 됩니다. 이 값들이 새 테이블의 열 이름이 됩니다. 주의할 점은 이 값들이 id_phong의 전체 값 목록일 필요는 없으며, 필요한 일부 값만 선택적으로 지정할 수 있다는 것입니다.

FOR id_phong IN ([30], [45])

예제용 DDL / DML 스크립트

데이터베이스가 있고 위의 PIVOT 사용 설명서에 있는 예제들을 직접 실습해 보고 싶다면, 아래의 DDL/DML 스크립트가 필요합니다.

DDL - 데이터 정의어(Data Definition Language)는 PIVOT 절 예제에서 사용할 테이블 생성 명령(CREATE TABLE)입니다.

CREATE TABLE phong
( id_phong INT NOT NULL,
  ten_phong VARCHAR(50) NOT NULL,
  CONSTRAINT pk_phong PRIMARY KEY (id_phong)
);

CREATE TABLE nhanvien
( so_nhanvien INT NOT NULL,
  ho VARCHAR(50) NOT NULL,
  ten VARCHAR(50) NOT NULL,
  luong INT,
  id_phong INT,
  CONSTRAINT pk_nhanvien PRIMARY KEY (so_nhanvien)
);

DML - 데이터 조작어(Data Manipulation Language)는 테이블에 필요한 데이터를 생성하는 INSERT 문입니다.

INSERT INTO phong
(id_phong, ten_phong)
VALUES
(30, 'Ketoan');

INSERT INTO phong
(id_phong, ten_phong)
VALUES
(45, 'Banhang');

INSERT INTO nhanvien
(so_nhanvien, ho, ten, luong, id_phong)
VALUES
(12009, 'Nguyen', 'Huong', 54000, 45);

INSERT INTO nhanvien
(so_nhanvien, ho, ten, luong, id_phong)
VALUES
(34974, 'Pham', 'Hoa', 80000, 45);

INSERT INTO nhanvien
(so_nhanvien, ho, ten, luong, id_phong)
VALUES
(34987, 'Phan', 'Lan', 42000, 45);

INSERT INTO nhanvien
(so_nhanvien, ho, ten, luong, id_phong)
VALUES
(45001, 'Tran', 'Hue', 57500, 30);

INSERT INTO nhanvien
(so_nhanvien, ho, ten, luong, id_phong)
VALUES
(75623, 'Vu', 'Hong', 65000, 30);