萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 動態行轉列完全指南:從靜態到參數化的四種實現方式

文章大綱

  • 背景脈絡:為什麼需要動態行轉列?
  • 方法一:靜態 CASE WHEN 拼接
  • 方法二:動態 SQL 拼接(支援 SQL Server 2000+)
  • 方法三:靜態 PIVOT 運算符(SQL Server 2005+)
  • 方法四:動態 PIVOT 運算符
  • 進階:參數化動態 PIVOT 通用腳本

背景脈絡

行轉列(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

方法一:靜態 CASE WHEN 拼接

最傳統、相容性最佳的寫法,適用於所有 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]

方法二:動態 SQL 拼接(SQL Server 2000+ 適用)

透過動態拼接欄位清單,自動適應資料中實際存在的科目,無需手工維護欄位。

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 運算符(SQL Server 2005+)

PIVOT 語法更符合關聯式思維,程式碼可讀性較高,但欄位仍需靜態指定。

靜態 PIVOT 結果

圖 4:靜態 PIVOT 執行結果

SELECT *
FROM (
    SELECT [UserName], [Subject], [Source]
    FROM [TestRows2Columns]
) p
PIVOT (
    SUM([Source]) FOR [Subject] IN ([數學],[英語],[語文])
) AS pvt
ORDER BY pvt.[UserName]

方法四:動態 PIVOT 運算符

結合動態 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)

進階:參數化動態 PIVOT 通用腳本

將「表名、分組欄位、行轉列欄位、數值欄位」抽象為變數,實現真正的一次編寫、到處復用。

-- =============================================
-- 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 查詢...

只需修改上方四個變數值,即可直接套用至任何資料表,大幅降低維護成本。

copyright © 萬盛學電腦網 all rights reserved