Báo cáo nhanh với PIVOT và GROUPING

Bạn làm việc cho 1 công ty bán hàng qua mạng. Ngày kia buồn tình, sếp của bạn bảo "tôi muốn có 1 bản báo cáo với số lượng sản phẩm mà công ty mình bán được, tại các thành phố khác nhau, trong mỗi năm khác nhau, và toàn bộ từ trước tới nay, ứng với mỗi tháng từ 1-12."

Điều quan trọng nhất là trong CSDL của bạn phải lưu trữ đầy đủ thông tin cho yêu cầu trên, nhưng lấy dữ ra rồi biểu thị thích hợp cho yêu cầu đó cũng không kém phần rắc rối.

Giả sử trong CSDL của bạn đã có bảng (hoặc bạn có thể tạo VIEW từ các bảng khác) như sau:

 

Với bạn, dữ liệu từ bảng hay VIEW đó có thể đủ để bạn trả lời cho yêu cầu nêu trên. Nhưng bạn cũng nhận ra rằng nó chỉ thích hợp cho mục đích lập trình hay lưu trữ dữ liệu, hoàn toàn không thích hợp cho 1 bản báo cáo. Thử tưởng tượng công ty bạn đã hoạt động hơn 10 năm, trên 30 tỉnh thành, với 100 sản phẩm khác nhau, bạn sẽ thấy nó sẽ rối rắm thế nào. Còn với sếp của bạn, cái bảng hay VIEW ấy chỉ là 1 đống rác. Sếp thường kém thông minh và lười suy nghĩ, nhưng vẫn là sếp :) . Bạn phải có 1 bản báo cáo đơn giản, dễ hiểu, rõ ràng cho sếp.

Đây là bản báo cáo sếp bạn mong muốn:


 

 

Thế thì từ bảng hay VIEW trên kia, bạn chuyển thành bản báo cáo này như thế nào ?

2 mệnh đề được sử dụng rộng rãi nhất trong báo cáo là PIVOT và GROUPING.

Và đoạn mã dưới đây đã chứng minh điều đó. PIVOT và GROUPING đã biến đống rác thành 1 bản báo cáo mà ngay cả người như sếp của bạn cũng có thể hiểu được dễ dàng :)

declare @bang table(MaSanPham varchar(50), ThanhPho varchar(50), SoLuong int, Nam int, Thang int)
insert into @bang values('IPhone','CanTho', 5, 2013, 1),
('IPhone','CanTho', 3, 2013, 1),('TuLanh','CanTho', 7, 2013, 1),
('TV','AnGiang', 2, 2014, 1),('IPhone','CanTho', 7, 2013, 2),
('TuLanh','AnGiang', 5, 2014, 3),('IPhone','CanTho', 3, 2013, 7),
('TuLanh','AnGiang', 2, 2014, 12),('TV','AnGiang', 2, 2013, 1),
('TuLanh','CanTho', 8, 2014, 11),('TV','AnGiang', 8, 2014, 7),
('TuLanh','AnGiang', 9, 2014, 2),('IPhone','CanTho', 8, 2014, 8),
('IPhone','AnGiang', 1, 2013, 2),('IPhone','AnGiang', 4, 2014, 10)
 
;with cte as(
select Nam, ThanhPho, MaSanPham, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] from
(select Nam, ThanhPho, MaSanPham, Soluong, Thang from @bang) c
pivot (sum(soluong) for Thang in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) p
)
, tam as (
select  Nam, ThanhPho, MaSanPham,
sum(isnull([1],0))[1],sum(isnull([2],0))[2],sum(isnull([3],0))[3],sum(isnull([4],0))[4],sum(isnull([5],0))[5],sum(isnull([6],0))[6],
sum(isnull([7],0))[7],sum(isnull([8],0))[8],sum(isnull([9],0))[9],sum(isnull([10],0))[10],sum(isnull([11],0))[11],sum(isnull([12],0))[12]
from cte group by grouping sets((Nam,ThanhPho,MaSanPham),(Nam,ThanhPho),(Nam),())
)
select
isnull(convert(varchar(20),Nam),'TongCong') Nam
, isnull(isnull(ThanhPho,'TongCong - '+convert(varchar(4),Nam)),'') ThanhPho
, isnull(isnull(MaSanPham,'TongCong - '+convert(varchar(4),Nam)+' - '+ThanhPho),'') MaSanPham
,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
,[1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12] as TongCong
from tam

Chú Ý :
Tùy công ty, báo cáo có thể rất đa dạng và rất phức tạp. Cách thức đúng đắn nhất là thiết kế những CSDL dạng lập thể, các báo cáo được thực hiện bằng các công cụ như SSAS. Những ứng dụng này được gọi chung là Business Intelligence. Đây là đề tài rất lớn và chỉ những công ty lớn hoặc chuyên ngành phân tích dữ liệu mới thực hiện những ứng dụng này. Giải pháp nêu trên chỉ dành cho những báo cáo nhanh, có tính nhất thời.