TRANSACT-SQL: Ví Dụ MINH HọA Về PIVOT Và UNPIVOT

PIVOT, trong các hệ quản trị cơ sở dữ liệu, là lệnh tổng hợp dữ liệu cho phép chuyển dữ liệu trong một cột của một Table thành các trường dữ liệu của một Table khác. Lệnh này cho phép người dùng có thể chọn một trường dữ liệu làm tiêu chí, từ đó “chiếu” các dữ liệu khác lên trường dữ liệu này để quan sát.

PIVOT

Để minh hoạ cho lệnh PIVOT, ta tạo một bảng dữ liệu về đơn đặt hàng của một cửa hàng bán đồ điện tử (Laptop, điện thoại di động, .v.v.).

Đầu tiên, chuyển vào làm việc trong CSDL tempdb, sau đó tạo bảng Sales_Order và chèn dữ liệu minh họa vào bảng:

use tempdb;

if exists (select * from sysobjects where name='Sales_Order' and type='U')
drop table Sales_Order;

CREATE TABLE Sales_Order
(
	Customer varchar(20), 
	Product varchar(10), 
	Quantity int
);

insert into Sales_Order values ('Minh Thu',  	 'Laptop',  	3);
insert into Sales_Order values ('Minh Thu',  	 'iPhone',  	2);
insert into Sales_Order values ('Minh Thu',  	 'Laptop',  	5);
insert into Sales_Order values ('Tuan Anh',  	 'Laptop',  	3);
insert into Sales_Order values ('Tuan Anh',  	 'iPhone',  	3);
insert into Sales_Order values ('Tuan Anh',  	 'iPhone',  	4);

Thực hiện lệnh Select để xem dữ liệu đã chèn đúng chưa?

SELECT * FROM Sales_Order;

Kết quả lệnh Select có dạng như sau:

Bảng dữ liệu minh họa

Sau khi đã có đầy đủ dữ liệu rồi, ta thực hiện việc thống kê thông tin về tổng số hàng mà mỗi khách hàng đặt mua cho mỗi loại hàng. Sử dụng lệnh PIVOT áp dụng cho cột Product và tính tổng cột Quantity, ta được:

SELECT * FROM Sales_Order
PIVOT (SUM(Quantity) FOR Product IN ([Laptop],[iPhone])) AS PivotedOrder;

Thực thi câu lệnh SQL này, ta được:

Kết quả thực thi Select với Pivot

Lưu ý, ta có thể Select tiếp từ kết quả của PIVOT trên như sau:

SELECT Customer, [Laptop] FROM 
(SELECT * FROM Sales_Order) AS OriginalOrder
PIVOT (SUM(Quantity) FOR Product IN ([Laptop],[iPhone])) AS PivotedOrder;

Khi đó, kết quả sẽ co hẹp lại:

Kết quả sẽ co hẹp lại

UNPIVOT

Lệnh UNPIVOT có chức năng ngược lại với chức năng của lệnh PIVOT. Xét ví dụ sau:

if exists (select * from sysobjects 
		where name='Sales_PivotedOrder' and type='U')
drop table Sales_PivotedOrder;

CREATE TABLE Sales_PivotedOrder
(
	Customer varchar(20), 
	Laptop int, 
	iPhone int
);

insert into Sales_PivotedOrder values ('Minh Thu',  	 8,  	2);
insert into Sales_PivotedOrder values ('Tuan Anh',  	 3,  	7);

select * from Sales_PivotedOrder;

Thực thi các lệnh trên, ta được bảng có dạng sau:

Bảng dữ liệu minh họa ví dụ Select - Unpivot

Thực thi lệnh UNPIVOT trên bảng Sales_PivotedOrder trên:

SELECT Customer, Product, Quantity FROM Sales_PivotedOrder
UNPIVOT (Quantity FOR Product IN ([Laptop],[iPhone])) AS UnPVT;

Ta thu được kết quả:

Kết quả thực thi lệnh Select - Unpivot

Các bạn có thể tải mã nguồn của ví dụ minh họa trên để thử nghiệm.

Transact-SQL: Ví dụ minh họa về PIVOT và UNPIVOT

PIVOT

UNPIVOT

Phạm Ngọc Hùng

Comments