SQL Server: Hàm pivot và unpivot

Tổng quan

PIVOT xoay một biểu thức có giá trị trong bảng bằng cách chuyển các giá trị duy nhất từ một cột trong biểu thức thành nhiều cột ở đầu ra. Và PIVOT chạy các tập hợp ở những nơi chúng được yêu cầu trên bất kỳ giá trị cột còn lại nào muốn có trong đầu ra cuối cùng. UNPIVOT thực hiện thao tác ngược lại với PIVOT bằng cách xoay các cột của biểu thức có giá trị trong bảng thành các giá trị cột. 

Cú pháp

Pivot

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;

Ví dụ

Sử dụng pivot

Bảng students có các dự liệu như sau

Sử dụng câu lệnh pivot như sau: 
​select name, math, history from students
pivot 
(
 avg(mark) for subject in ( math, history)
)
as pivotedTable

Kết quả

Lưu ý

Khi các hàm tổng hợp được sử dụng với PIVOT, sự hiện diện của bất kỳ giá trị null nào trong cột giá trị sẽ không được xem xét khi tính toán hàm tổng hợp.

Sử dụng unpivot

Từ câu lệnh để tạo ra bảng kết quả sau khi sử dụng pivot, ta có thể áp dụng unpivot như sau

select name, subject, average from (
select name, math, history from students
pivot 
(
 avg(mark) for subject in ( math, history)
)
as pivotedTable
) as pvtTable
unpivot(
average for subject in (math, history)
) as unpivotTable

Kết quả: 

Lưu ý

UNPIVOT không phải là phép biến đổi ngược lại hoàn toàn của PIVOT vì không thể đảm bảo sau khi sử dụng UNPIVOT thì dữ liệu quay trở về ban đầu (trước khi sử dụng lệnh PIVOT) thực hiện tổng hợp và hợp nhất nhiều hàng có thể thành một hàng duy nhất ở đầu ra. UNPIVOT không tái tạo kết quả biểu thức có giá trị trong bảng ban đầu vì các hàng đã được hợp nhất. Ngoài ra, giá trị null ở đầu vào sẽ UNPIVOT biến mất ở đầu ra. Khi các giá trị biến mất, điều đó cho thấy có thể có các giá trị null ban đầu trong đầu vào trước khi thực PIVOT hiện thao tác.