Mệnh đề PIVOT trong SQL Server

PIVOT SQL được sử dụng như thế nào? Dưới đây là những điều bạn cần biết về cách dùng PIVOT trong SQL Server.

Nếu đang nghiên cứu về lập trình ứng dụng và quản lý cơ sở dữ liệu, ắt hẳn bạn đã từng nghe tới SQL Server. Đây là kiến thức cơ bản mà bạn cần nắm khi muốn phát triển trong ngành lập trình.

Hệ thống quản lý cơ sở dữ liệu quan hệ SQL Server có các chức năng cốt lõi bao gồm tạo, thao tác và lưu trữ dữ liệu rất hiệu quả. SQL Server chứa tất cả đặc điểm này và có giao diện cài đặt cực kỳ thân thiện với người dùng, không giống như các máy chủ cơ sở dữ liệu khác mà yêu cầu cấu hình dòng lệnh mở rộng.

Trong bài viết này, chúng ta sẽ tìm hiểu về toán tử PIVOT trong SQL Server. Chúng ta sẽ tìm hiểu cách sử dụng toán tử Pivot với các tập dữ liệu đơn giản, các cột động cũng như với hàm tổng hợp như AVG() và SUM().

PIVOT là một công cụ rất hiệu quả nếu chúng ta đang thực hiện phân tích dữ liệu trên một tập dữ liệu lớn và muốn tổng hợp dữ liệu theo hàng để lấy kết quả có ý nghĩa từ chúng.

Toán tử PIVOT trong SQL Server được giới thiệu vào năm 2005. Đây là một toán tử mạnh mẽ được sử dụng để chuyển đổi các hàng của bảng thành các cột. Nó thường được sử dụng để tạo ra một tập kết quả tóm tắt giúp phân tích những tập dữ liệu lớn dễ dàng.

Bài viết dưới đây sẽ cung cấp cho bạn kiến thức cơ bản về cách dùng toán tử PIVOT in SQL Server. Toán tử PIVOT giống như các toán tử quan hệ mà cho phép chuyển đổi biểu thức có giá trị trong bảng vào bảng khác. Thực tế, cả toán tử PIVOT và UNPIVOT đều tạo báo cáo đa chiều, giúp kết hợp và so sánh một số lượng lớn dữ liệu thật nhanh.

Bạn có thể dùng toán tử PIVOT khi cần chuyển đổi biểu thức giá trị bảng. Nó tách các giá trị duy nhất từ một cột thành nhiều cột trong kết quả cuối cùng. Nó cũng tổng hợp các giá trị cột còn lại trong kết quả cuối cùng. Giờ hãy cùng nhau đi sâu vào tìm hiểu chi tiết hơn nhé!

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

Cú pháp mệnh đề PIVOT

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

Tên biến hoặc giá trị biến

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

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

giatri_chuyen1, giatri_chuyen2, … giatri_chuyen_n: Danh sách các giá trị cần chuyển.

bang_nguon: Lệnh SELECT đưa 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ính tổng như SUM trong SQL Server, COUNT, MIN, MAX hay AVG.

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

cot_chuyen: Cột chứa giá trị cần chuyển.

bidanh_bang_chuyen: Bí danh của bảng sau khi chuyển.

Mệnh đề PIVOT có thể dùng trong các phiên bản sau của SQL Server: SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.

Để thực hiện theo các bước trong hướng dẫn, hãy xem phần DDL để tạo bảng và DML để tạo dữ liệu ở cuối bài viết này rồi thử chạy trên chính cơ sở dữ liệu của bạn.

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

Ta có bảng nhanvien với các dữ liệu như dưới đây.

so_nhanvien	ho	ten	luong	id_phong
12009	Nguyen	Huong	54000	45
34974	Pham	Hoa	80000	45
34987	Phan	Lan	42000	45
45001	Tran	Hua	57500	30
75623	Vu	Hong	65000	30

Chạy lệnh SQL dưới đây để tạo truy vấn chéo bằng 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ư dưới đây.

TongLuongTheoPhong	30	45
TongLuong	122500	176000

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

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

Trước tiên cần xác định trường thông tin nào muốn đưa vào bảng chuyển. Ở ví dụ này là TongLuong làm cột đầu tiên, sau đó là 2 cột id_phong 30 và id_phong 45.

SELECT “TongLuong’ AS TongLuongTheoPhong,
[30], [45]

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

Tiếp theo là xác định lệnh SELECT sẽ trả về dữ liệu nguồn cho bảng mới.

Ở ví dụ này là id_phong và luong từ bảng nhanvien.

(SELECT id_phong, luong
FROM nhanvien) AS BangNguon

Cần chỉ ra bí danh cho truy vấn nguồn, trong ví dụ này là BangNguon.

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

Hàm có thể dùng trong truy vấn chéo gồm SUM, COUNT, MIN, MAX và AVG. Ở ví dụ này là hàm tính tổng SUM.

PIVOT
(SUM(luong)

Xác định giá trị cần chuyển

Cuối cùng là giá trị cần chuyển để đưa vào kết quả. Đây sẽ là tiêu đề cột trong truy vấn chéo.

Ở ví dụ này, chúng ta chỉ cần trả về id_phòng 30 và 45. Các giá trị này sẽ là tên cột trong bảng mới. Cần nhớ là những giá trị này là danh sách có giới hạn của các giá trị 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 các ví dụ

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

DDL - Data Definition Language là các lệnh tạo bảng (CREATE TABLE) để dùng trong ví dụ về 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 - Data Manipulation Language 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’, ‘Hue’, 57500, 30);

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

Những lỗi thường gặp khi dùng PIVOT trong SQL Server

PIVOT không hợp nhất nhiều hàng

Hãy xét ví dụ bên dưới. Chúng ta sẽ triển khai cả PIVOT và UNPIVOT cho bảng này và so sánh kết quả của cả hai bảng.

SELECT Name,[Year] , Sales FROM
(
   SELECT [Year], Pankaj,Rahul,Sandeep FROM
   (SELECT Name, [Year] , Sales FROM Employee )Tab1
PIVOT
(
   SUM(Sales) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2
)Tab
UNPIVOT
(
   Sales FOR Name IN (Pankaj,Rahul,Sandeep)
) AS TAb2

Kết quả:

Bạn có thể thấy hai bảng cho kết quả giống nhau.

Toán tử UNPIVOT

Nó là phương thức ngược lại của toán tử PIVOT trong SQL Server. Toán tử này thực hiện thao tác ngược lại với PIVOT bằng cách chuyển đổi dữ liệu từ các cột sang hàng. Toán tử UNPIVOT cũng xoay bảng PIVOT thành bảng bình thường. Nó được giới thiệu lần đầu tiên trong phiên bản SQL Server 2005.

Cú pháp:

Cú pháp sau minh họa UNPIVOT trong SQL Server:

SELECT (column_names)   
FROM (table_name)   
UNPIVOT  
 (   
   Aggregate_function (column to be aggregated)  
   FOR PivotColumn IN (pivot column values)  
 ) AS (alias_name)  

Ví dụ:

Hãy xem xét ví dụ sau để hiểu cách hoạt động của UNPIVOT. Đầu tiên, chúng ta sẽ tạo một bảng gốc và PIVOT, sau đó áp dụng toán tử UNPIVOT trên bảng này.

Đoạn code sau khai báo lần đầu một biến bảng tạm @Tab:

DECLARE @Tab TABLE    
(    
   Year int,    
   North varchar(45),  
   South varchar(45)  
)  

Tiếp theo, bạn sẽ chèn các giá trị vào bảng như sau:

INSERT INTO @Tab    
SELECT Year, North, South FROM     
(SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1    
PIVOT    
(SUM(Sales) FOR Region IN (North, South)) AS PivotTable    
ORDER BY PivotTable.Year  

Giờ triển khai UNPIVOT vào lệnh dưới:

SELECT Region, Year, Sales FROM @Tab t    
UNPIVOT    
(    
Sales FOR Region IN (North, South)    
) AS UnpivotTable  

Kết quả:

Như bạn thấy pivot SQL Server là một mệnh đề hữu ích trong lập trình. Nó giúp người dùng chuyển đổi dữ liệu trong bảng dễ dàng. Hi vọng bài viết giúp bạn hiểu rõ hơn về cách dùng Pivot SQL Server.