SQL 行轉列、列轉行與動態交叉表完整實作教學

案例一：列轉行（行轉欄，Pivot）

原始資料：學生成績明細表

目標結果：

靜態 Pivot（科目固定）

SELECT name,
       SUM(CASE km WHEN '數學' THEN cj END) AS 數學,
       SUM(CASE km WHEN '英語' THEN cj END) AS 英語,
       SUM(CASE km WHEN '語文' THEN cj END) AS 語文
FROM test
GROUP BY name;

動態 Pivot（科目不固定，自動產生欄位）

DECLARE @sql NVARCHAR(MAX) = N'SELECT name';

SELECT @sql += N', SUM(CASE km WHEN ' + QUOTENAME(km, '''') + N' THEN cj END) AS ' + QUOTENAME(km)
FROM (SELECT DISTINCT km FROM test) AS t;

SET @sql += N' FROM test GROUP BY name';

PRINT @sql;  -- 除錯用
EXEC sp_executesql @sql;

案例二：彙總統計列轉行

原始資料：

目標：依性別計算平均年齡，呈現為欄位

動態 SQL 實作

DECLARE @sql NVARCHAR(MAX) = N'SELECT ';

SELECT @sql += N'SUM(CASE sex WHEN ' + QUOTENAME(sex, '''') + N' THEN avg_age END) AS ' + QUOTENAME(sex) + N','
FROM (SELECT sex, AVG(age) AS avg_age FROM t GROUP BY sex) AS a;

SET @sql = LEFT(@sql, LEN(@sql) - 1) + N' FROM (SELECT sex, AVG(age) AS avg_age FROM t GROUP BY sex) AS a';

EXEC sp_executesql @sql;

案例三：行轉列

原始資料：寬表（一列多欄位）

CREATE TABLE t1 (a INT, b INT, c INT, d INT, e INT, f INT, g INT);
INSERT INTO t1 VALUES (1,2,3,4,5,6,7);

目標：轉為長表（每欄位一列）

UNION ALL 靜態寫法

SELECT 'a' AS col_name, a AS col_value FROM t1 UNION ALL
SELECT 'b', b FROM t1 UNION ALL
SELECT 'c', c FROM t1 UNION ALL
SELECT 'd', d FROM t1 UNION ALL
SELECT 'e', e FROM t1 UNION ALL
SELECT 'f', f FROM t1 UNION ALL
SELECT 'g', g FROM t1;

動態 UNPIVOT（SQL 2005+）

SELECT col_name, col_value
FROM t1
UNPIVOT (col_value FOR col_name IN (a,b,c,d,e,f,g)) AS u;

跨資料庫通用動態行轉列（利用系統目錄）

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(name), ',')  -- SQL 2017+
FROM sys.columns
WHERE object_id = OBJECT_ID('t1');

SET @sql = N'SELECT col_name, col_value FROM t1 UNPIVOT (col_value FOR col_name IN (' + @cols + N')) AS u';
EXEC sp_executesql @sql;

進階技巧：多欄位交叉表

若需同時依「年級」與「科目」交叉顯示「平均分數」與「及格率」，可結合 GROUPING SETS、CUBE、ROLLUP 或多層 CASE 運算，或使用報表工具（SSRS、Power BI）呈現更靈活。

效能與維護建議

• 靜態優於動態：欄位固定時優先用靜態 SQL，有執行計畫快取、免 SQL 注入風險、除錯容易。
• 動態 SQL 防注入：務必使用 QUOTENAME、sp_executesql 參數化，避免直接拼接字串。
• 大量資料慎用：Pivot/Unpivot 涉及掃描與聚合，百萬列以上建議預先彙總至摘要表，或使用 Columnstore 索引加速。
• 文件化：動態 SQL 輸出結果欄位不固定，前端報表需對應處理，務必在註解說明預期欄位來源邏輯。