Một vài điều căn bản cần lưu ý về UNPIVOT

UNPIVOT là phương pháp chuyển cách thức biểu thị dữ liệu từ cột sang dòng. Giả thử bạn có bảng sau đây:

Bây giờ bạn muốn biểu thị như sau:

Có 2 cách để thực hiện điều này. Về mặt tối ưu, cả 2 cách gần như nhau. Cách 1 là phương pháp thông thường, khá rườm rà nhưng có thể biến hóa dễ dàng hơn. Cách 2 là phương pháp sử dụng cú pháp UNPIVOT. Cách 2 gọn gàng hơn nhiều và trông có vẻ PRO hơn, nên được nhiều người ưa thích. Tuy nhiên vì cách 2 ngắn gọn nên nó ẩn chứa những lô-gíc, cần phải hiểu rõ để áp dụng cho chính xác.

Dưới đây là toàn bộ mã của cả 2 phương pháp để bạn chạy thử. Bạn có thể tự lý giải ở phương pháp 1. Còn phương pháp 2, tôi sẽ có 1 vài phân tích.

-- script

declare @temp table (maKH int , sanPhamA int, sanPhamB int, sanPhamC int, sanPhamD int )

insert into  @temp values

(1000,12,32,0,11),(3000,56,32,0,50),(1000,12,32,67,30),(4000,0,32,0,70),

(2000,0,32,0,77),(2000,12,32,0,20),(2000,0,32,0,50),(1000,12,32,19,90),

(3000,25,0,0,53),(1000,44,24,45,50),(2000,12,32,45,50),(4000,12,32,18,34),

(4000,0,32,44,10),(5000,0,32,0,50),(2000,34,0,11,50),(4000,12,32,55,20),

(5000,78,32,70,80),(5000,0,37,0,40),(4000,55,32,67,50),(4000,12,32,77,0)

select * from @temp

-- cach thong thuong

select maKH,sanPham,

sum(

case 

when sanPham = 'sanPhamA' then sanPhamA  

when sanPham = 'sanPhamB' then sanPhamB 

when sanPham = 'sanPhamC' then sanPhamC 

when sanPham = 'sanPhamD' then sanPhamD

end

) tongCong

from @temp

cross join (

select 'sanPhamA' as sanPham 

union all select 'sanPhamB' 

union all select 'sanPhamC' 

union all select 'sanPhamD') e

group by maKH, sanPham

order by maKH, sanPham

-- su dung UNPIVOT

select maKH, sanPham, tongCong

from (select maKH, sum(sanPhamA) sanPhamA, sum(sanPhamB) sanPhamB,sum(sanPhamC) sanPhamC, sum(sanPhamD) sanPhamD from @temp group by maKH) e

unpivot(tongCong for sanPham in([sanPhamA],[sanPhamB],[sanPhamC],[sanPhamD]) ) u;

-- script

 

Xét phương pháp sử dụng UNPIVOT

 

- Bảng gốc có các trường sau : maKH, sanPhamA, sanPhamB, sanPhamC, sanPhamD

- Bảng kết quả có 2 trường mới : sanPhame, tongCong.

Có thể chia UNPIVOT thành 3 phần với thứ tự câu lệnh :

1.

select maKH, sanPham, tongCong

 

2.

from (select maKH, sum(sanPhamA) sanPhamA, sum(sanPhamB) sanPhamB,sum(sanPhamC) sanPhamC, sum(sanPhamD) sanPhamD from @temp group by maKH) e

3.

unpivot(tongCong for sanPham in([sanPhamA],[sanPhamB],[sanPhamC],[sanPhamD]) ) u;

Nhưng về mặt lô-gíc, trình tự được sắp xếp như sau:

1.

from (select maKH, sum(sanPhamA) sanPhamA, sum(sanPhamB) sanPhamB,sum(sanPhamC) sanPhamC, sum(sanPhamD) sanPhamD from @temp group by maKH) e

2.

unpivot(tongCong for sanPham in([sanPhamA],[sanPhamB],[sanPhamC],[sanPhamD]) ) u;

3.

select maKH, sanPham, tongCong

- Trước hết ở bước 1, bảng gốc được tóm gọn bằng GROUP BY với SUM để tạo tính duy nhất của maKH trong bảng. Nếu bảng gốc của bạn đã có maKH ở dạng duy nhất thì bạn không cần phải sử dụng thêm bước đệm với GROUP BY mà có thể sử dụng thẳng bảng gốc như :

from @temp

- Tiếp theo bước 2, UNPIVOT, 2 trường mới được thêm vào là tongCong và sanPham. Trường sanPham đại diện cho danh sách các trường sanPham mà bạn đã thấy ở bảng gốc. Còn trường tongCong là giá trị của các trường sanPham ấy (dòng) ở bảng gốc.

- Bước 3 rất đơn giản, chỉ biểu thị 3 trường, maKH (bảng gốc), tongCong, sanPham (2 trường mới).

Để dễ hiểu hơn, ta có thể giả sử maKH trong bảng gốc là duy nhất. Khi đó câu lệnh được viết như sau:

select maKH, sanPham, tongCong

from @temp 

unpivot(tongCong for sanPham in([sanPhamA],[sanPhamB],[sanPhamC],[sanPhamD]) ) u;

Bạn cần lưu tâm 3 điểm sau:

• 1 - sanPham in([sanPhamA],[sanPhamB],[sanPhamC],[sanPhamD]) : sanPham là trường mới đại diện cho nhóm sanPham ở bảng gốc. 
• 2 - tongCong for sanPham : tongCong là trường mới, đại diện cho giá trị của sản phẩm ở 1
• 3 - maKH (trường chính) chỉ được đề cập ở select, tức bước cuối cùng, sau khi mọi việc đã xong.