Những khái niệm căn bản cần hiểu rõ khi sử dụng PIVOT

Pivot là phương pháp để chuyển biểu thị từ dòng sang cột, với 1 dạng tính toán nào đó. 

Trước hết, hãy xét bảng ví dụ dưới đây: 

declare @temp table (id int identity(1,1), a int, b int, c int) 
insert into @temp values 
(1,1,7),(4,1,6),(3,2,8),(1,2,1),(2,1,3),(3,2,5), 
(1,1,2),(4,1,7),(3,4,9),(4,2,2),(1,1,3),(1,2,4), 
(3,3,2),(2,3,5),(4,2,9),(4,3,7),(1,3,1),(4,2,6); 

Dưới đây là dữ liệu bảng và kết quả lệnh truy xuất mà bạn mong muốn

 
 

 

Có 2 cách căn bản để truy xuất được kết quả này 
1 - 
select a, 
sum(case when b=1 then c end) as '1', 
sum(case when b=2 then c end) as '2', 
sum(case when b=3 then c end) as '3', 
sum(case when b=4 then c end) as '4' 
from @temp group by a; 

2 - 
select a, [1],[2],[3],[4] 
from (select a, b, c from @temp) o 
-- from @temp 
pivot(sum(c) for b in([1],[2],[3],[4])) p; 

Cách 1 dài dòng hơn, nhưng linh động hơn. Cách 2 là cách mới, trông có vẻ "pro" hơn nhưng kém linh động và dễ có lỗi nếu không hiểu kỹ. Nhưng về mặt tối ưu, cả hai cách đều giống nhau. 

Để hiểu rõ cách thức hoạt động của PIVOT, chúng ta cần tìm hiểu kết hợp cách 1 và 2, và lưu tâm các điểm sau đây: 

Có 4 nhân tố liên quan đến PIVOT 

A - nhóm biểu thị theo dòng (cột a) 
B - nhóm biểu thị theo cột (cột b) 
C - thực hiện tính toán (cột c) 
D - biểu thị các cột 

Ở ví dụ 1, A chính là cột a được khẳng định bởi GROUP BY và biểu thị trong SELECT. Ở ví dụ 2, cột a chỉ được sử dụng trong SELECT, không có trong hàm PIVOT 

Ở ví dụ 1, B được sử dụng trong CASE với các giá trị của nó. Ở ví dụ 2, B được sử dụng trong hàm PIVOT với IN cùng các giá trị của nó, đồng thời nó phải tồn tại trong SELECT của bảng ảo o. 

Ở ví dụ 1, C chính là kết quả của hàm tính toán (SUM), sau khi xác định bởi CASE. Ở ví dụ 2, C được sử dụng hàm PIVOT với hàm tính toán (SUM). 

Ở ví dụ 1, D được biểu thị qua các kết quả của CASE và SUM. Ở ví dụ 2, D được xác định trong IN ở hàm PIVOT và biểu thị qua SELECT. 

Ngoài ra, một điểu cần lưu tâm khác, và cũng là phần tạo ra nhiều lẫn lộn cho các lập trình viên, là bảng ảo o khi sử dụng phương pháp 2. 

Mục đích của bảng ảo o là chỉ lấy những cột liên quan đến PIVOT (a,b,c) để sử dụng. Nếu bảng của bạn chỉ có cột liên quan đến quá trình PIVOT thì bạn không cần đến bảng ảo o. Như ví dụ này, giả sử bảng gốc của bạn không có cột id, thì bạn có thể dùng ngay bảng @temp chứ không phải qua bảng ảo o. 

Trong cách 2, các cột không có trong hàm PIVOT thì mặc nhiên được dùng cho nhóm biểu thị theo dòng (A). Điều này giải thích vì sao trong cách 2, ở hàm PIVOT bạn không thấy cột a, đồng thời phẩn SELECT trong bảng ảo o bạn chỉ thấy cột a, b, và c chứ không có cột id. Nếu trong phần SELECT của bảng ảo o bạn dùng * hay id,a,b,c thì cột id và a sẽ được dùng cho nhóm biểu thị dòng (A), điều này cho kết quả sai với yêu cầu ban đầu. 

Với logic như thế, chắc bạn đã hiểu vì sao nếu không có cột id trong bảng @temp thì bạn có thể dùng thẳng bảng @temp, chứ không cần phải dùng qua bảng ảo o (như phẩn comment out) trong ví dụ 2.