SQL 資料行轉列 (PIVOT) 完整實作：靜態與動態兩種方案

測試資料建立

CREATE TABLE Test (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50),
    subject NVARCHAR(50),
    Source NUMERIC(18,0)
);

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

方案一：靜態 PIVOT (欄位固定、已知)

SELECT name,
       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 Test
GROUP BY name;

結果：每人一列，三科分數分欄顯示。

方案二：動態 PIVOT (欄位不固定、依資料決定)

聚合版 (SUM)

DECLARE @sql VARCHAR(8000) = 'SELECT name,';
SELECT @sql += 'SUM(CASE subject WHEN ''' + subject + ''' THEN Source ELSE 0 END) AS ''' + subject + ''','
FROM (SELECT DISTINCT subject FROM Test) AS a;
SET @sql = LEFT(@sql, LEN(@sql)-1) + ' FROM Test GROUP BY name';
EXEC(@sql);

明細版 (不彙總，直接列出)

DECLARE @sql VARCHAR(8000) = 'SELECT id,';
SELECT @sql += '(CASE subject WHEN ''' + subject + ''' THEN Source ELSE NULL END) AS ''' + subject + ''','
FROM (SELECT DISTINCT subject FROM Test) AS a;
SET @sql = LEFT(@sql, LEN(@sql)-1) + ' FROM Test';
EXEC(@sql);

補充：SQL Server 2005+ 內建 PIVOT/UNPIVOT

-- 內建語法 (靜態)
SELECT name, [數學], [英語], [語文]
FROM (
    SELECT name, subject, Source FROM Test
) AS src
PIVOT (SUM(Source) FOR subject IN ([數學], [英語], [語文])) AS pvt;

動態 PIVOT 仍需拼接字串執行，邏輯與上述 CASE 方案相同。

選擇建議