SQL Server 動態行轉列（PIVOT）完整實作：靜態/動態、參數化、條件篩選

測試資料準備

CREATE TABLE TestRows2Columns (
    Id INT IDENTITY(1,1) NOT NULL,
    UserName NVARCHAR(50) NULL,
    Subject NVARCHAR(50) NULL,
    Source NUMERIC(18,0) NULL
);

INSERT INTO TestRows2Columns (UserName, Subject, Source) VALUES
(N'張三', N'語文', 60), (N'李四', N'數學', 70), (N'王五', N'英語', 80),
(N'王五', N'數學', 75), (N'王五', N'語文', 57), (N'李四', N'語文', 80),
(N'張三', N'英語', 100);

方法一：靜態 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 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
SET @sql = LEFT(@sql, LEN(@sql)-1) + ' FROM [TestRows2Columns] GROUP BY [UserName]'
EXEC(@sql)

方法三：靜態 PIVOT（SQL 2005+）

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

方法四：動態 PIVOT

DECLARE @sql_col VARCHAR(8000), @sql_str 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'
EXEC(@sql_str)

方法五：參數化動態 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 + ']'
EXEC sp_executesql @sql_str, N'@sql_col_out NVARCHAR(MAX) OUTPUT', @sql_col_out = @sql_col OUTPUT

-- 執行動態 PIVOT
SET @sql_str = N'SELECT * FROM (SELECT [' + @groupColumn + '],[' + @row2column + '],[' + @row2columnValue + '] FROM [' + @tableName + ']) p
PIVOT (SUM([' + @row2columnValue + ']) FOR [' + @row2column + '] IN (' + @sql_col + ')) AS pvt ORDER BY pvt.[' + @groupColumn + ']'
EXEC(@sql_str)

方法六：帶條件篩選的參數化動態 PIVOT

DECLARE @sql_where NVARCHAR(MAX) = 'WHERE UserName = ''王五'''
-- 取得欄位清單時加入 WHERE
SET @sql_str = N'SELECT @sql_col_out = ISNULL(@sql_col_out + '','','') + QUOTENAME([' + @row2column + ']) FROM [' + @tableName + '] ' + @sql_where + ' GROUP BY [' + @row2column + ']'
-- 執行 PIVOT 時同樣加入 WHERE
SET @sql_str = N'SELECT * FROM (SELECT [' + @groupColumn + '],[' + @row2column + '],[' + @row2columnValue + '] FROM [' + @tableName + ']' + @sql_where + ') p
PIVOT (SUM([' + @row2columnValue + ']) FOR [' + @row2column + '] IN (' + @sql_col + ')) AS pvt ORDER BY pvt.[' + @groupColumn + ']'
EXEC(@sql_str)