Mệnh đề PIVOT trong SQL Server

Trong SQL Server (Transact-SQL), mệnh đề PIVOT cho phép tạo bảng chéo (cross-tabulation) để chuyển đổi dữ liệu từ hàng thành cột, tức là lấy kết quả tổng hợp và xoay dữ liệu từ dạng dòng sang dạng cột.

Ví dụ tính tổng và chuyển hàng thành cột trong bảng dữ liệu

Cú pháp mệnh đề PIVOT

SELECT cot_dautien AS bidanh_cot_dautien,
 [giatri_chuyen1], [giatri_chuyen2], … [giatri_chuyen_n]
FROM
 (SELECT ... 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;

Tham số

cot_dautien: Cột hoặc biểu thức sẽ trở thành cột đầu tiên trong bảng mới sau khi xoay.

bidanh_cot_dautien: Tên của cột đầu tiên trong bảng mới sau khi chuyển đổi.

giatri_chuyen1, giatri_chuyen2, … giatri_chuyen_n: Danh sách các giá trị cần chuyển đổi (sẽ thành tên cột trong bảng kết quả).

bang_nguon: Câu lệnh SELECT lấy dữ liệu nguồn (dữ liệu ban đầu) vào bảng mới.

bidanh_bang_nguon: Bí danh của bang_nguon.

ham_tong: Hàm tổng hợp như SUM, COUNT, MIN, MAX hoặc AVG.

cot_tong: Cột hoặc biểu thức dùng với ham_tong.

cot_chuyen: Cột chứa giá trị sẽ được chuyển đổi.

bidanh_bang_chuyen: Bí danh của bảng sau khi xoay.

Mệnh đề PIVOT có thể sử dụng trong các phiên bản SQL Server: 2014, 2012, 2008 R2, 2008 và 2005.

Ví dụ với mệnh đề PIVOT

Chúng ta có bảng dữ liệu như sau:

Chạy lệnh SQL dưới đây để tạo truy vấn chéo với mệnh đề PIVOT:

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;

Kết quả trả về sẽ như sau:

Ví dụ trên tạo một bảng sau khi xoay dữ liệu, hiển thị tổng lương của phòng có ID 30 và phòng có ID 45. Kết quả nằm trên một hàng với 2 cột, mỗi cột đại diện cho một phòng.

Các bước xây dựng truy vấn PIVOT

1. Xác định cột trong bảng mới của truy vấn chéo

Đầu tiên cần xác định các trường thông tin muốn đưa vào bảng xoay. Trong ví dụ này, TongLuong là cột đầu tiên, tiếp theo là 2 cột id_phong 30 và id_phong 45.

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

2. Xác định dữ liệu trong bảng nguồn

Tiếp theo là câu lệnh SELECT sẽ trả về dữ liệu nguồn cho bảng mới. Trong ví dụ này là id_phong và luong từ bảng nhanvien.

(SELECT id_phong, luong
 FROM nhanvien) AS BangNguon

Cần chỉ định bí danh cho truy vấn nguồn, ở ví dụ này là BangNguon.

3. Xác định hàm tính tổng

Các hàm có thể dùng trong truy vấn chéo bao gồm SUM, COUNT, MIN, MAX và AVG. Trong ví dụ này dùng hàm SUM.

PIVOT
(SUM(luong)

4. Xác định giá trị cần chuyển đổi

Cuối cùng, giá trị cần chuyển đổi để đưa vào kết quả. Đây sẽ là tiêu đề cột trong truy vấn chéo. Trong ví dụ này, chỉ cần trả về id_phong 30 và 45. Các giá trị này sẽ là tên cột trong bảng mới. Lưu ý rằng đây là danh sách các giá trị giới hạn của id_phong và không nhất thiết phải chứa tất cả các giá trị.

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

DDL / DML cho ví dụ

Nếu bạn có cơ sở dữ liệu và muốn thử các ví dụ trong hướng dẫn PIVOT ở trên, bạn sẽ cần DDL / DML.

DDL - Ngôn ngữ định nghĩa dữ liệu là các lệnh tạo bảng (CREATE TABLE) để dùng trong ví dụ mệnh đề PIVOT.

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 - Ngôn ngữ thao tác dữ liệu là các lệnh INSERT để tạo dữ liệu cần thiết cho bảng.

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', 'Hua', 57500, 30);
INSERT INTO nhanvien (so_nhanvien, ho, ten, luong, id_phong) VALUES (75623, 'Vu', 'Hong', 65000, 30);