Sử dụng Pivot và UnPivot trong Sql server

Mục đích:
Trong nhiều bài toán xử lý số liệu cần hiển thị số liệu từ dòng sang cột hay ngược lại phục vụ cho vấn đề so sánh đối chiếu hay xử lý số liệu. Sql server có nhiều cách để thực hiện vấn đề này. Trong đó có môt phương pháp rất tối ưu đó là sử dụng PIVOT và UNPIVOT

Thực hiện:
+ PIVOT
Khi bạn muốn tạo một cross-tabulation để báo cáo số liệu một các trực quan tóm tắt dữ liệu.

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 GO -- Create datadabse CREATE DATABASE DataTest   GO   USE DataTest   GO   -- Create table temp IF OBJECT_ID('Tempdb..#Sale', 'U') IS NOT NULL     DROP TABLE #Sale   CREATE TABLE #Sale (EmployeeID INT, [Month] INT, Amount FLOAT) INSERT INTO #Sale VALUES (1, 1, 1234) INSERT INTO #Sale VALUES (1, 2, 123) INSERT INTO #Sale VALUES (1, 4, 234) INSERT INTO #Sale VALUES (1, 6, 321)   INSERT INTO #Sale VALUES (2, 3, 1234) INSERT INTO #Sale VALUES (3, 5, 123) INSERT INTO #Sale VALUES (3, 4, 234) INSERT INTO #Sale VALUES (3, 7, 321)   -- Report SELECT EmployeeID, [1] AS Jan, [2] AS Feb, [3] AS Mar, [4] AS Apr, [5] AS May, [6] AS Jun, [7] AS Jul FROM (SELECT EmployeeID, [Month], Amount FROM #Sale) p PIVOT (SUM (Amount) FOR [Month] IN ([1], [2], [3], [4], [5], [6], [7])) AS pvt ORDER BY EmployeeID   DROP TABLE #Sale   GO

Kết quả:

? 1 2 3 4 5 6 7 EmployeeID  Jan    Feb    Mar    Apr    May    Jun    Jul ----------- ------ ------ ------ ------ ------ ------ ------ 1           1234   123    NULL   234    NULL   321    NULL 2           NULL   NULL   1234   NULL   NULL   NULL   NULL 3           NULL   NULL   NULL   234    123    NULL   321   (3 row(s) affected)

+ UNPIVOT
Và ngược lại với PIVOT. Bạn muốn chuyển số liệu từ cột sang dòng

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 -- Create table temp IF OBJECT_ID('Tempdb..#Order', 'U') IS NOT NULL     DROP TABLE #Order   CREATE TABLE #Order(EmployeeID INT, Jan INT, Feb INT, Mar INT, Apr INT) INSERT INTO #Order VALUES (1, 12, 23, 1, NULL) INSERT INTO #Order VALUES (2, 21, 13, 4, 5) INSERT INTO #Order VALUES (3, 9, 8, 2, 3) INSERT INTO #Order VALUES (4, NULL, 1, 4, 7)   --Unpivot the table. SELECT EmployeeID, [Month], Orders FROM (SELECT EmployeeID, Jan, Feb, Mar, Apr FROM #Order) p UNPIVOT (Orders FOR [Month] IN (Jan, Feb, Mar, Apr))AS unpvt GO

Kết quả

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 EmployeeID  Month Orders ----------- ----- ----------- 1           Ja    12 1           Fe    23 1           Ma    1 2           Ja    21 2           Fe    13 2           Ma    4 2           Ap    5 3           Ja    9 3           Fe    8 3           Ma    2 3           Ap    3 4           Fe    1 4           Ma    4 4           Ap    7   (14 row(s) affected)