PIVOT函數常用于數據的行轉列，同時也可以用此函數實現類似于Excel中的數據透視表的效果。

PIVOT函數

PIVOT 函數的基本語法如下：

-- PIVOT 語法
SELECT <非透視的列>,
    [第一個透視的列] AS <列名稱>,
    [第二個透視的列] AS <列名稱>,
    ...
    [最后一個透視的列] AS <列名稱>,
FROM
    (<生成數據的 SELECT 查詢>) 
    AS <源查詢的別名>
PIVOT
(
    <聚合函數>(<要聚合的列>)
FOR
[<包含要成為列標題的值的列>]
    IN ( [第一個透視的列], [第二個透視的列],
    ... [最后一個透視的列])
) AS <透視表的別名>
<可選的 ORDER BY 子句>;

為了更好的解釋Povit函數的使用，下面建立一張測試用的臨時數據表：

CREATE TABLE #ShoppingCart(  [Name] nvarchar(8) NOT NULL, [Category] nvarchar(8) NOT NULL,  [TotalPrice] DECIMAL DEFAULT(0) NOT NULL  )  
INSERT INTO #ShoppingCart([Name],[Category],[TotalPrice])  
SELECT '張三','餅干',30 UNION ALL
SELECT '張三','面包',10 UNION ALL  
SELECT '張三','果凍',30 UNION ALL 
SELECT '李四','餅干',40 UNION ALL
SELECT '李四','面包',20 UNION ALL   
SELECT '李四','果凍',20 UNION ALL  
SELECT '陳小二','餅干',20 UNION ALL 
SELECT '陳小二','餅干',50 UNION ALL  
SELECT '陳小二','面包',30 UNION ALL  
SELECT '陳小二','果凍',30  

生成后的表結構如下：

現在如果需要降此數據進行分類匯總，得到每個人對應類別的價格加總，有如下兩種方法：
第一種方法是使用Case when語句計算：

SELECT [Name],
SUM(CASE [Category] WHEN '餅干' THEN [TotalPrice] ELSE 0 END) AS '餅干',
SUM(CASE [Category] WHEN '果凍' THEN [TotalPrice] ELSE 0 END) AS '果凍',
SUM(CASE [Category] WHEN '面包' THEN [TotalPrice] ELSE 0 END) AS '面包'     
FROM [#ShoppingCart]
GROUP BY [Name]

第二種方法則是使用povit函數來生成

select * from #ShoppingCart pivot(sum([TotalPrice]) for [Category] in (餅干,果凍,面包)) a

兩個語句生成的結果是一致的

注意：PIVOT、UNPIVOT是SQL Server 2005 的語法，使用需滿足數據庫的兼容級別，需要兼容級別大于90，否則計算出的結果可能會與上面的截圖不同。
查詢兼容級別語法如下：
SELECT compatibility_level FROM sys.databases WHERE name = '數據庫名';

如果你是跟著上述代碼生成的測試數據，則數據表是一張臨時表，所以對應的數據庫名是Tempdb,
SELECT compatibility_level FROM sys.databases WHERE name = 'Tempdb';

修改兼容級別語法如下：
ALTER DATABASE 數據庫名 SET COMPATIBILITY_LEVEL = 90（必須是當前數據庫版本范圍內的兼容級別）
不同數據庫版本支持的兼容級別如下：

動態(tài)PIVOT行轉列

在日常的數據處理中經常會遇到要轉化為列字段的數量特別多的情況，這時如果用上面的語句填寫要填寫非常多的字段，所以需要使用動態(tài)代碼進行處理
···
–動態(tài)PIVOT行轉列
DECLARE @sql_str VARCHAR(8000)
DECLARE @sql_col VARCHAR(8000)
SELECT @sql_col = ISNULL(@sql_col + ‘,’,‘’) + QUOTENAME([Category]) FROM #ShoppingCart GROUP BY [Category]–這一句用于選出所有的列標題并加上[]號，然后連接起來
SET @sql_str = ’
SELECT * FROM #ShoppingCart PIVOT
(SUM([TotalPrice]) FOR [Category] IN ( ‘+ @sql_col +’) ) AS pvt’
PRINT (@sql_str)
EXEC (@sql_str)
···

Unpovit函數

UNPIVOT函數,與上述功能相反,把列轉成行。我們直接使用WITH關鍵字把上述PIVOT查詢當成源表,然后再使用UNPIVOT關鍵把它旋轉回原來的模樣

WITH Pvt AS 
(
select * from #ShoppingCart  pivot(sum([TotalPrice]) for [Category] in ([餅干],[果凍],[面包])) as a 
)
SELECT  [Name],[Category],[TotalPrice]
FROM 　　Pvt
UNPIVOT(  [TotalPrice] FOR [Category] in ([餅干],[果凍],[面包]) )AS T

結果如下，可以看到如果用PIVOT 執(zhí)行聚合，并將多行合并為輸出中的一行，UNPIVOT就只能輸出合并后的一行數據了。 另外PIVOT函數也不會輸出值為Null的數據行，所以UNPIVOT 同樣也無法復原原始數據中原有的null值

代碼下載：

百度網盤：

鏈接: https://pan.baidu.com/s/1FiQWnWb6xZ4f32HDSkcqwg?pwd=tyby

提取碼: tyby

到此這篇關于SQL Povit函數使用及實例的文章就介紹到這了,更多相關SQL Povit函數使用內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！