CongChien Blog Chia sẻ kiến thức, trao đổi kinh nghiệm, các thủ thuật về lập trình, quản trị mạng
Trong quá trình lấy số liệu, thiết lập bảng biểu và báo cáo, một trường hợp thường gặp là bạn phải hiển thị dữ liệu từ dạng dòng chuyển thành cột. Kiểu truy vấn như vậy gọi là Crosstab-Query.
Từ phiên bản SQL Server 2000 trở về trước, không có một lệnh nào trực tiếp hỗ trợ việc truy xuất như vậy. Đa số sử dụng các câu lệnh trung gian như CASE, CURSOR. Từ phiên bản SQL Server 2005, Microsoft đã giới thiệu một lệnh mới, là PIVOT để hỗ trợ việc truy xuất dữ liệu.
Bài viết này sẽ mô tả cách thức truy vấn crosstab trên bằng cả hai cách, dùng các câu lệnh trung gian và cả PIVOT.
Dữ liệu đầu vào : CSDL Northwind, bảng Orders
Đây là Sample Database có sẵn trên SQL Server 2000, ở các phiên bản SQL Server sau này, bạn phải tự down về chứ không có trong bộ cài nữa.
Bảng Orders có nhiều cột, tuy nhiên để lấy ví dụ cho bài viết này ta chỉ quan tâm đến 3 cột là:
| OrderID | ID của đơn hàng. Trong bảng Orders này thì OrderID là khóa chính | |
| EmployeeID | ID của nhân viên. Với 1 đơn hàng thì có 1 và chỉ 1 nhân viên bán ra đơn hàng đó | |
| ShipVia | Đơn hàng này được vận chuyển qua phương thức nào. Thuộc tính này chỉ có 3 giá trị là 1,2 và 3 đại diện cho 3 phương thức vận chuyển khác nhau. |
Cách diễn giải 3 trường này có thể không khớp với mô tả CSDL Northwind của Microsoft nhưng do tôi lấy dữ liệu trong này để làm ví dụ nên cũng không cần quá quan trọng về vấn đề này.
Bài toán
Lập báo cáo số đơn hàng bán được bởi mỗi nhân viên, chia theo từng loại vận chuyển. Ví dụ:
| EmployeeID | ShipVia1 | ShipVia2 | ShipVia3 |
| 1 | 21 | 33 | 54 |
| 2 | 44 | 23 | 11 |
Đây là dữ liệu cần lấy để tạo báo cáo:
SELECT OrderID, EmployeeID, ShipVia FROM Orders
Cách xuất dữ liệu thông thường đơn giản chỉ truy vấn như sau:
SELECT EmployeeID, ShipVia, COUNT(1) So_luong FROM dbo.Orders GROUP BY EmployeeID, ShipVia ORDER BY EmployeeID
Tuy nhiên dạng kết quả như thế này không phù hợp với yêu cầu bài toán đưa ra. Mặc dù dựa vào số liệu này hoàn toàn có thể thiết kế được 1 bảng biểu theo yêu cầu.
Dạng báo cáo hiển thị kết quả từ dạng dòng (ở đây là các giá trị của ShipVia) thành dạng cột (tạo thành các cột ShipVia1, ShipVia2 và ShipVia3 tương ứng với 3 giá trị của ShipVia là 1,2 và 3) được gọi là Crosstab-Report. Và câu truy vấn để thực hiện gọi là Crosstab-Query.
Cách làm 1: Tạo Crosstab-Report bằng phát biểu CASE
Ở phiên bản SQL Server 2000 trở về trước, ta cần dùng phát biểu CASE để thực hiện
SELECT EmployeeID, COUNT(CASE ShipVia WHEN 1 THEN 1 END) ShipVia1, COUNT(CASE ShipVia WHEN 2 THEN 1 END) ShipVia2, COUNT(CASE ShipVia WHEN 3 THEN 1 END) ShipVia3, COUNT(1) Total FROM dbo.Orders GROUP BY EmployeeID ORDER BY EmployeeID
Kết quả
Câu truy vấn được thực hiện như sau:
– Ta thực hiện một lệnh GROUP BY theo các nhân viên.
– Tại mỗi một Group, tiến hành đếm số lượng các đơn hàng theo từng loại vận chuyển
– Dòng COUNT(1) Total cho phép ta tạo thêm 1 cột nữa, để tính tổng số đơn hàng của mỗi nhân viên bán ra. Dòng này là tùy chọn.
Ngoài ra khi dùng CASE kết hợp với GROUP BY, ta có thể sử dụng thêm một chức năng nữa, là tạo ra một dòng cuối cùng tổng kết (summary) các dòng trong bảng bằng cách đưa thêm lựa chọn WITH CUBE hoặc ROLLUP
SELECT EmployeeID, COUNT(CASE ShipVia WHEN 1 THEN 1 END) ShipVia1, COUNT(CASE ShipVia WHEN 2 THEN 1 END) ShipVia2, COUNT(CASE ShipVia WHEN 3 THEN 1 END) ShipVia3, COUNT(1) Total FROM dbo.Orders GROUP BY EmployeeID WITH CUBE ORDER BY EmployeeID
Như ta thấy, thực hiện câu lệnh trên sẽ tạo ra một dòng nữa có EmployeeID là NULL, và các giá trị trong dòng này là tổng các giá trị ở các dòng còn lại trong bảng kết quả.
Tuy nhiên có một điều bất tiện là khi thực hiện sắp xếp, giá trị NULL lại được đưa lên đầu.
Nếu làm việc trên Oracle, có thể bạn đã quen với mệnh đề ORDER BY có thêm thuộc tính NULL_FIRSTS hoặc NULL_LASTS để cho phép xếp giá trị NULL lên đầu tiên hoặc cuối cùng.
Vậy trên SQL Server thì sao? Ta có thể thực hiện như sau.
SELECT EmployeeID, COUNT(CASE ShipVia WHEN 1 THEN 1 END) ShipVia1, COUNT(CASE ShipVia WHEN 2 THEN 1 END) ShipVia2, COUNT(CASE ShipVia WHEN 3 THEN 1 END) ShipVia3, COUNT(1) Total FROM dbo.Orders GROUP BY EmployeeID WITH CUBE ORDER BY ( CASE WHEN EmployeeID IS NULL THEN 1 ELSE 0 END ), EmployeeID
Như thế nếu EmployeeID là NULL (dòng tổng) thì kết quả sẽ là 1, và được xếp ở cuối cùng. Ngược lại nếu không null, thì lại dựa vào EmployeeID để Order tiếp. Lệnh CASE thật là kì diệu phải không?
Cách làm 2: Dùng lệnh PIVOT
Lệnh PIVOT mới được đưa vào từ phiên bản SQL Server 2005:
SELECT EmployeeID, [1] ShipVia1, [2] ShipVia2, [3]ShipVia3, [1] + [2] + [3] Total FROM ( SELECT EmployeeID, ShipVia, OrderID FROM dbo.Orders ) SourceTable PIVOT(COUNT(OrderID) FOR ShipVia IN ([1], [2], [3])) PivotTable ORDER BY EmployeeID
Giải thích
Bạn có thể xem MSDN để hiểu rõ hơn, cũng như biết thêm các cách sử dụng nâng cao của PIVOT. Ở đây tôi chỉ giải thích ngắn gọn và giới thiệu cách sử dụng đơn giản nhất.
Đầu tiên, quan trọng nhất là tạo bảng SourceTable như trên. Đây là bảng dữ liệu đầu vào trước khi thực hiện PIVOT
( SELECT EmployeeID, ShipVia, OrderID FROM dbo.Orders ) SourceTable
Tiếp theo là bảng PIVOT, cho phép tạo các hàm tính toán và thực hiện hoán chuyển hàng thành cột dựa trên dữ liệu đầu vào là bảng SourceTable
PIVOT(COUNT(OrderID) FOR ShipVia IN ([1], [2], [3])) PivotTable
Chú ý đoạn FOR ShipVia IN ([1], [2], [3]) ở đây là các giá trị cho thuộc tính ShipVia.
Kết luận
Cuối cùng ta có thể tổng kết lại như sau
– Dạng báo cáo Crosstab cung cấp cái nhìn trực quan hơn về dữ liệu trong một số trường hợp.
– Tuy nhiên khi phát sinh thêm cột dữ liệu cần phải thay đổi lại câu truy vấn. Điều này rõ ràng không thuận tiện bằng việc báo cáo xuất theo dạng dòng như trước.
– Tuy SQL Server cung cấp tính năng PIVOT từ bản 2005 nhưng rất nhiều người vẫn dùng cách CASE như ở phiên bản trước, trong đó có cả tôi. Lý do thì tùy mỗi người. Với tôi thì đó là sự đơn giản, rõ ràng, và rất thuận tiện để tạo các dòng tổng kết.
Nguồn: sqlviet[dot]com