行轉列(Pivot)是資料庫開發中極常見的需求,將垂直儲存的資料轉為水平報表格式。雖然網路上範例眾多,但許多存在硬式編碼、缺乏參數化等問題。本文將從基礎語法進階到完全參數化的動態 PIVOT 腳本,只需修改四個變數(表名、分組欄位、行轉列欄位、數值欄位)即可套用至任意環境,並附上完整執行效果圖示。

圖 1:行轉列最終效果示意
先建立測試表 TestRows2Columns 並插入模擬成績資料:

圖 2:原始樣本資料
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestRows2Columns]') AND type in (N'U'))
DROP TABLE [dbo].[TestRows2Columns]
GO
CREATE TABLE [dbo].[TestRows2Columns](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NULL,
[Subject] [nvarchar](50) NULL,
[Source] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
INSERT INTO [TestRows2Columns] ([UserName],[Subject],[Source])
SELECT N'張三',N'語文',60 UNION ALL
SELECT N'李四',N'數學',70 UNION ALL
SELECT N'王五',N'英語',80 UNION ALL
SELECT N'王五',N'數學',75 UNION ALL
SELECT N'王五',N'語文',57 UNION ALL
SELECT N'李四',N'語文',80 UNION ALL
SELECT N'張三',N'英語',100
GO
最傳統、相容性最佳的寫法,適用於所有 SQL Server 版本。缺點是欄位必須寫死,新增科目需修改程式碼。

圖 3:靜態 CASE WHEN 執行結果
SELECT [UserName],
SUM(CASE [Subject] WHEN N'數學' THEN [Source] ELSE 0 END) AS [數學],
SUM(CASE [Subject] WHEN N'英語' THEN [Source] ELSE 0 END) AS [英語],
SUM(CASE [Subject] WHEN N'語文' THEN [Source] ELSE 0 END) AS [語文]
FROM [TestRows2Columns]
GROUP BY [UserName]
透過動態拼接欄位清單,自動適應資料中實際存在的科目,無需手工維護欄位。
DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT [UserName],'
SELECT @sql = @sql + 'SUM(CASE [Subject] WHEN ''' + [Subject] + ''' THEN [Source] ELSE 0 END) AS ' + QUOTENAME([Subject]) + ','
FROM (SELECT DISTINCT [Subject] FROM [TestRows2Columns]) AS a
SELECT @sql = LEFT(@sql, LEN(@sql)-1) + ' FROM [TestRows2Columns] GROUP BY [UserName]'
PRINT(@sql)
EXEC(@sql)
PIVOT 語法更符合關聯式思維,程式碼可讀性較高,但欄位仍需靜態指定。

圖 4:靜態 PIVOT 執行結果
SELECT *
FROM (
SELECT [UserName], [Subject], [Source]
FROM [TestRows2Columns]
) p
PIVOT (
SUM([Source]) FOR [Subject] IN ([數學],[英語],[語文])
) AS pvt
ORDER BY pvt.[UserName]
結合動態 SQL 與 PIVOT,自動蒐集欄位清單並建構查詢。
DECLARE @sql_str VARCHAR(8000), @sql_col VARCHAR(8000)
SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME([Subject])
FROM [TestRows2Columns] GROUP BY [Subject]
SET @sql_str = '
SELECT * FROM (
SELECT [UserName],[Subject],[Source] FROM [TestRows2Columns]
) p PIVOT (
SUM([Source]) FOR [Subject] IN (' + @sql_col + ')
) AS pvt
ORDER BY pvt.[UserName]'
PRINT (@sql_str)
EXEC (@sql_str)
將「表名、分組欄位、行轉列欄位、數值欄位」抽象為變數,實現真正的一次編寫、到處復用。
-- =============================================
-- Author: 聽風吹雨
-- Create date: 2014.05.26
-- Description: 參數化動態 PIVOT 行轉列
-- =============================================
DECLARE @sql_str NVARCHAR(MAX), @sql_col NVARCHAR(MAX)
DECLARE @tableName SYSNAME = 'TestRows2Columns' -- 行轉列表
DECLARE @groupColumn SYSNAME = 'UserName' -- 分組欄位
DECLARE @row2column SYSNAME = 'Subject' -- 行變列欄位
DECLARE @row2columnValue SYSNAME = 'Source' -- 數值欄位
-- 動態取得欄位清單
SET @sql_str = N'SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME([' + @row2column + ']) FROM ' + @tableName + ' GROUP BY [' + @row2column + ']'
-- 後續以 sp_executesql 參數化執行,組裝最終 PIVOT 查詢...
只需修改上方四個變數值,即可直接套用至任何資料表,大幅降低維護成本。