SQL?Povit函數(shù)使用及實(shí)例詳解
PIVOT函數(shù)常用于數(shù)據(jù)的行轉(zhuǎn)列,同時(shí)也可以用此函數(shù)實(shí)現(xiàn)類似于Excel中的數(shù)據(jù)透視表的效果。
PIVOT函數(shù)
PIVOT 函數(shù)的基本語(yǔ)法如下:
-- PIVOT 語(yǔ)法 SELECT <非透視的列>, [第一個(gè)透視的列] AS <列名稱>, [第二個(gè)透視的列] AS <列名稱>, ... [最后一個(gè)透視的列] AS <列名稱>, FROM (<生成數(shù)據(jù)的 SELECT 查詢>) AS <源查詢的別名> PIVOT ( <聚合函數(shù)>(<要聚合的列>) FOR [<包含要成為列標(biāo)題的值的列>] IN ( [第一個(gè)透視的列], [第二個(gè)透視的列], ... [最后一個(gè)透視的列]) ) AS <透視表的別名> <可選的 ORDER BY 子句>;
為了更好的解釋Povit函數(shù)的使用,下面建立一張測(cè)試用的臨時(shí)數(shù)據(jù)表:
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
生成后的表結(jié)構(gòu)如下:
現(xiàn)在如果需要降此數(shù)據(jù)進(jìn)行分類匯總,得到每個(gè)人對(duì)應(yīng)類別的價(jià)格加總,有如下兩種方法:
第一種方法是使用Case when語(yǔ)句計(jì)算:
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函數(shù)來(lái)生成
select * from #ShoppingCart pivot(sum([TotalPrice]) for [Category] in (餅干,果凍,面包)) a
兩個(gè)語(yǔ)句生成的結(jié)果是一致的
注意:PIVOT、UNPIVOT是SQL Server 2005 的語(yǔ)法,使用需滿足數(shù)據(jù)庫(kù)的兼容級(jí)別,需要兼容級(jí)別大于90,否則計(jì)算出的結(jié)果可能會(huì)與上面的截圖不同。
查詢兼容級(jí)別語(yǔ)法如下:SELECT compatibility_level FROM sys.databases WHERE name = '數(shù)據(jù)庫(kù)名';
如果你是跟著上述代碼生成的測(cè)試數(shù)據(jù),則數(shù)據(jù)表是一張臨時(shí)表,所以對(duì)應(yīng)的數(shù)據(jù)庫(kù)名是Tempdb,SELECT compatibility_level FROM sys.databases WHERE name = 'Tempdb';
修改兼容級(jí)別語(yǔ)法如下:ALTER DATABASE 數(shù)據(jù)庫(kù)名 SET COMPATIBILITY_LEVEL = 90(必須是當(dāng)前數(shù)據(jù)庫(kù)版本范圍內(nèi)的兼容級(jí)別)
不同數(shù)據(jù)庫(kù)版本支持的兼容級(jí)別如下:
動(dòng)態(tài)PIVOT行轉(zhuǎn)列
在日常的數(shù)據(jù)處理中經(jīng)常會(huì)遇到要轉(zhuǎn)化為列字段的數(shù)量特別多的情況,這時(shí)如果用上面的語(yǔ)句填寫(xiě)要填寫(xiě)非常多的字段,所以需要使用動(dòng)態(tài)代碼進(jìn)行處理
···
–動(dòng)態(tài)PIVOT行轉(zhuǎn)列
DECLARE @sql_str VARCHAR(8000)
DECLARE @sql_col VARCHAR(8000)
SELECT @sql_col = ISNULL(@sql_col + ‘,’,‘’) + QUOTENAME([Category]) FROM #ShoppingCart GROUP BY [Category]–這一句用于選出所有的列標(biāo)題并加上[]號(hào),然后連接起來(lái)
SET @sql_str = ’
SELECT * FROM #ShoppingCart PIVOT
(SUM([TotalPrice]) FOR [Category] IN ( ‘+ @sql_col +’) ) AS pvt’
PRINT (@sql_str)
EXEC (@sql_str)
···
Unpovit函數(shù)
UNPIVOT函數(shù),與上述功能相反,把列轉(zhuǎn)成行。我們直接使用WITH關(guān)鍵字把上述PIVOT查詢當(dāng)成源表,然后再使用UNPIVOT關(guān)鍵把它旋轉(zhuǎn)回原來(lái)的模樣
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
結(jié)果如下,可以看到如果用PIVOT 執(zhí)行聚合,并將多行合并為輸出中的一行,UNPIVOT就只能輸出合并后的一行數(shù)據(jù)了。 另外PIVOT函數(shù)也不會(huì)輸出值為Null的數(shù)據(jù)行,所以UNPIVOT 同樣也無(wú)法復(fù)原原始數(shù)據(jù)中原有的null值
代碼下載:
百度網(wǎng)盤(pán):
鏈接: https://pan.baidu.com/s/1FiQWnWb6xZ4f32HDSkcqwg?pwd=tyby
到此這篇關(guān)于SQL Povit函數(shù)使用及實(shí)例的文章就介紹到這了,更多相關(guān)SQL Povit函數(shù)使用內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
SQL Server 聚焦存儲(chǔ)過(guò)程性能優(yōu)化、數(shù)據(jù)壓縮和頁(yè)壓縮提高IO性能方法(一)
這篇文章主要介紹了SQL Server 聚焦存儲(chǔ)過(guò)程性能優(yōu)化、數(shù)據(jù)壓縮和頁(yè)壓縮提高IO性能方法(一),非常不錯(cuò),具有參考借鑒價(jià)值,需要的朋友可以參考下2017-02-02快速實(shí)現(xiàn)SQL Server數(shù)據(jù)庫(kù)恢復(fù)備份
這篇文章主要為大家詳細(xì)介紹了如何快速實(shí)現(xiàn)SQL Server數(shù)據(jù)庫(kù)恢復(fù)備份的兩種方法,感興趣的小伙伴們可以參考一下2016-05-05SQL參數(shù)化查詢的另一個(gè)理由 命中執(zhí)行計(jì)劃
為了提高數(shù)據(jù)庫(kù)運(yùn)行的效率,我們需要盡可能的命中執(zhí)行計(jì)劃,這樣就可以節(jié)省運(yùn)行時(shí)間2012-08-08sql server 2000數(shù)據(jù)庫(kù)備份還原的圖文教程
MSSQL是微軟公司的一款數(shù)據(jù)庫(kù)管理系統(tǒng),本文將詳細(xì)介紹MSSQL2000中數(shù)據(jù)庫(kù)的備份和還原功能,需要的朋友可以參考下2014-08-08