在許多的互聯網項目當中，報表開發是整個項目當中很重要的一個功能模塊。其中會有一些比較復雜的報表統計需要行轉列或者列轉行的需求。今天給大家簡單介紹一下在SQLServer當中如何使用PIVOT、UNPIVOT內置函數實現數據報表的行轉列、列轉行。有需要的朋友可以一起學習一下。

一、PIVOT、UNPIVOT用途

官方解釋：可以使用 PIVOT 和 UNPIVOT 關系運算符將表值表達式更改為另一個表。PIVOT 通過將表達式某一列中的唯一值轉換為輸出中的多個列來旋轉表值表達式，并在必要時對最終輸出中所需的任何其余列值執行聚合。UNPIVOT 與 PIVOT 執行相反的操作，將表值表達式的列轉換為列值。

注意：UNPIVOT運算符通過將列旋轉到行來執行PIVOT的反向操作，UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 執行聚合，并將多個可能的行合并為輸出中的一行。UNPIVOT 不重現原始表值表達式的結果，因為行已被合并。另外，UNPIVOT 輸入中的 NULL 值也在輸出中消失了。如果值消失，表明在執行 PIVOT 操作前，輸入中可能就已存在原始 NULL 值。

二、PIVOT語法格式

SELECT <非透視的列>,

????[第一個透視的列] AS <列名稱>,

????[第二個透視的列] AS <列名稱>,

????...

????[最后一個透視的列] AS <列名稱>,

FROM

????(<生成數據的 SELECT 查詢>)

????AS <源查詢的別名>

PIVOT

(

????<聚合函數>(<要聚合的列>)

FOR

[<包含要成為列標題的值的列>]

????IN ( [第一個透視的列], [第二個透視的列],

????... [最后一個透視的列])

) AS <透視表的別名>

<可選的 ORDER BY 子句>;

三、行轉列示例說明

-- 創建測試表 學習成績統計表
CREATE  TABLE ScoreStatistics
(UserName         NVARCHAR(20),        --學生姓名SubjectName       NVARCHAR(30),        --科目名稱Score            FLOAT,               --成績
)
-- 插入測試數據
INSERT INTO ScoreStatistics SELECT '小王', '語文', 100
INSERT INTO ScoreStatistics SELECT '小王', '數學', 90.5
INSERT INTO ScoreStatistics SELECT '小王', '英語', 88
INSERT INTO ScoreStatistics SELECT '小王', '歷史', 65
INSERT INTO ScoreStatistics SELECT '小李', '語文', 81
INSERT INTO ScoreStatistics SELECT '小李', '數學', 99
INSERT INTO ScoreStatistics SELECT '小李', '英語', 95
INSERT INTO ScoreStatistics SELECT '小李', '歷史', 90
INSERT INTO ScoreStatistics SELECT '小劉', '語文', 90
INSERT INTO ScoreStatistics SELECT '小劉', '數學', 85
INSERT INTO ScoreStatistics SELECT '小劉', '英語', 59
INSERT INTO ScoreStatistics SELECT '小劉', '歷史', 98
-- 傳統寫法
select UserName,max(case SubjectName when '語文' then Score else 0 end)語文,max(case SubjectName when '數學'then Score else 0 end)數學,max(case SubjectName when '英語'then Score else 0 end)英語,max(case SubjectName when '歷史'then Score else 0 end)歷史
from ScoreStatistics
group by UserName
-- PIVOT 寫法更簡潔
SELECT * FROM ScoreStatistics
AS P
PIVOT
(SUM(Score/*行轉列后 列的值*/) FORp.SubjectName/*需要行轉列的列*/ IN ([語文],[數學],[英語],歷史/*列的值*/)
) AS T
-- order by 語文 desc  具體科目排序
-- order by username desc -- 姓名排序
-- 動態拼接列的示例
DECLARE @sql_str VARCHAR(8000); -- 要執行的sql
--拿到數值列 [歷史],[數學],[英語],[語文]
DECLARE @sql_col VARCHAR(8000);
SELECT @sql_col = ISNULL(@sql_col + ',','') 
+ QUOTENAME(SubjectName) 
FROM ScoreStatistics GROUP BY SubjectName;
print(@sql_col); -- 打印數值列,不必需
SET @sql_str = '
SELECT * FROM (
SELECT [UserName],[SubjectName],[Score] FROM [ScoreStatistics]) 
p PIVOT
(SUM([Score]) FOR [SubjectName] IN ( '+ @sql_col +') ) AS pvt
ORDER BY pvt.[UserName]'
PRINT (@sql_str);--打印執行的sql
EXEC (@sql_str);-- 執行查詢

輸出結果：

UserName 語文 數學 英語 歷史

小王 100 90.5 88 65

小劉 90 85 59 98

小李 81 99 95 90

四、列轉行示例

-- 插入測試表CREATE  TABLE ScoreSummary(   UserName         NVARCHAR(20),        --學生姓名   數學        FLOAT,               --數學成績   英語             FLOAT,               --英語成績   語文             FLOAT,               --語文成績   歷史             FLOAT,               --歷史成績)-- 插入測試數據INSERT INTO ScoreSummary SELECT '小李',81,99,95,90;INSERT INTO ScoreSummary SELECT '小劉',90,85,59,98;INSERT INTO ScoreSummary SELECT '小王',100,90.5,88,65;-- 查詢用法select aa.UserName,aa.Scorefrom (select UserName,數學,英語,語文,歷史 from dbo.ScoreSummary) as aunpivot(Score for ScoreSummary in(數學,英語,語文,歷史)) as aa order by aa.UserName
輸出結果：

UserName Score

小李 81

小李 99

小李 95

小李 90

小劉 90

小劉 85

小劉 59

小劉 98

小王 100

小王 90.5

小王 88

小王 65

IT技術分享社區

個人博客網站：https://programmerblog.xyz

文章推薦 SQL常用語句大全（值得收藏） 辦公技巧：常用的100個Word快捷鍵！ GitHub上值得收藏的100個精選前端項目！ 數據庫優化：SQL 查找是否"存在"，別再 count 了，很耗費時間的！ 學習 MySQL 高性能優化原理，這一篇就夠了！ MySQL優化：數據量很大，分頁查詢很慢，有什么優化方案？ 數據庫：MySQL中，當update修改數據與原數據相同時會再次執行嗎？