CREATE TABLE Test (
id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(50),
subject NVARCHAR(50),
Score NUMERIC(18,0)
);
INSERT INTO Test (name, subject, Score) VALUES
(N'張三', N'語文', 60),
(N'李四', N'數學', 70),
(N'王五', N'英語', 80),
(N'王五', N'數學', 75),
(N'王五', N'語文', 57),
(N'李四', N'語文', 80),
(N'張三', N'英語', 100);
SELECT
name,
SUM(CASE subject WHEN N'數學' THEN Score ELSE 0 END) AS 數學,
SUM(CASE subject WHEN N'英語' THEN Score ELSE 0 END) AS 英語,
SUM(CASE subject WHEN N'語文' THEN Score ELSE 0 END) AS 語文
FROM Test
GROUP BY name;
結果:每位學生一列,科目為欄位,值為分數總和 (此處每人每科僅一筆)。
DECLARE @sql NVARCHAR(MAX) = N'SELECT name,';
SELECT @sql += N'
SUM(CASE subject WHEN ' + QUOTENAME(subject, '''') + N' THEN Score ELSE 0 END) AS ' + QUOTENAME(subject) + N','
FROM (SELECT DISTINCT subject FROM Test) AS s;
-- 移除最後逗號,補上 FROM/GROUP BY
SET @sql = LEFT(@sql, LEN(@sql) - 1) + N' FROM Test GROUP BY name';
EXEC sp_executesql @sql;
關鍵技巧:QUOTENAME(subject, '''') 自動處理單引號跳脫與括號包裹,防止注入與語法錯誤。
DECLARE @sql NVARCHAR(MAX) = N'SELECT id,';
SELECT @sql += N'
(CASE subject WHEN ' + QUOTENAME(subject, '''') + N' THEN Score ELSE NULL END) AS ' + QUOTENAME(subject) + N','
FROM (SELECT DISTINCT subject FROM Test) AS s;
SET @sql = LEFT(@sql, LEN(@sql) - 1) + N' FROM Test';
EXEC sp_executesql @sql;
此法產生稀疏矩陣:每學生一列,僅有該科目分數,其餘為 NULL。
SELECT name, [數學], [英語], [語文]
FROM (
SELECT name, subject, Score FROM Test
) src
PIVOT (
SUM(Score) FOR subject IN ([數學], [英語], [語文])
) pvt;
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(subject), ',') -- SQL 2017+
-- 舊版用 STUFF + FOR XML PATH
FROM (SELECT DISTINCT subject FROM Test) s;
SET @sql = N'
SELECT name, ' + @cols + N'
FROM (
SELECT name, subject, Score FROM Test
) src
PIVOT (SUM(Score) FOR subject IN (' + @cols + N')) pvt';
EXEC sp_executesql @sql;
| 方法 | 適用場景 | 優點 | 缺點 |
|---|---|---|---|
| 靜態 CASE | 列固定、簡單報表 | 可讀性高、計畫穩定、可建索引 | 結構變更需改代碼 |
| 動態 CASE SQL | 列動態、邏輯複雜 | 極度靈活 | 計畫難重用、防注入需 QUOTENAME、除錯難 |
| PIVOT | 列固定/半固定、標準語法 | 語意清晰、最佳化器支援好 | 動態仍需拼 SQL、不支援多彙總函數 |
列轉行本質是「將屬性值轉為屬性名」。靜態場景首選 CASE 或 PIVOT;動態場景需動態 SQL,務必使用 QUOTENAME 防注入。大量資料建議在應用層或報表工具 (SSRS/Power BI) 完成轉置,減輕資料庫壓力。