SQL 列轉行 (UNPIVOT) 實戰：以 UNION ALL 實現動態列轉換

情境說明

原始查詢透過 FULL JOIN 產生寬表：fritemcd, qty, qty2，需轉為長表格式：fritemcd, type, qty，其中 type 區分「出貨/入庫」。

標準 UNPIVOT 寫法 (SQL Server 2005+)

SELECT fritemcd, type, qty
FROM
(
    SELECT fritemcd, qty, qty2
    FROM (原始複雜查詢) AS src
) p
UNPIVOT
(
    qty FOR type IN (qty AS '出貨', qty2 AS '入庫')
) AS unpvt;

相容性更佳的 UNION ALL 寫法

SELECT fritemcd, '出貨' AS type, qty  AS qty FROM (原始查詢) WHERE qty  IS NOT NULL
UNION ALL
SELECT fritemcd, '入庫' AS type, qty2 AS qty FROM (原始查詢) WHERE qty2 IS NOT NULL;

優點：不支援 UNPIVOT 的舊版資料庫也能執行、執行計畫穩定、易於加入額外過濾條件。

效能優化建議

1. CTE 物件化原始查詢，避免 UNION ALL 重複掃描：

WITH src AS (原始查詢)
SELECT fritemcd, '出貨', qty  FROM src WHERE qty  IS NOT NULL
UNION ALL
SELECT fritemcd, '入庫', qty2 FROM src WHERE qty2 IS NOT NULL;

2. 加上覆蓋索引：CREATE INDEX IX_iniop03t_movedt_frstockcd ON iniop03t(movedt, frstockcd) INCLUDE (fritemcd, qty);
3. 避免 SELECT *，僅投影必要欄位，減少 I/O。

動態列轉行 (未知欄位數)

若欄位數不固定，需動態 SQL：

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(name), ',') FROM sys.columns WHERE object_id = OBJECT_ID('tempdb..#src');
SET @sql = 'SELECT fritemcd, type, qty FROM #src UNPIVOT (qty FOR type IN (' + @cols + ')) u';
EXEC sp_executesql @sql;

總結

針對固定兩欄 (qty/qty2) 的列轉行，UNION ALL + CTE 是最穩健、可維護的方案；僅當欄位動態時才引入動態 SQL，並注意注入風險。