Oracle 行轉列實戰：從 DECODE 到 PIVOT 的完整遷移指南

業務場景：銷售數據透視分析

某零售系統記錄每筆交易的「商品分類（DM1）」、「門店（DM2）」、「指標類型（MC1：c1/銷量、c2/客單價、c3/轉換率）」與「數值（VAL）」。管理層需按「分類+門店」維度，將三個指標橫向展開為三欄，供報表直接使用。

基礎資料準備

CREATE TABLE test (
    dm1 CHAR(3),
    dm2 CHAR(3),
    mc1 NVARCHAR2(20),
    val NVARCHAR2(20)
);

INSERT INTO test VALUES ('101','1','c1','100');
INSERT INTO test VALUES ('101','1','c2','80');
INSERT INTO test VALUES ('101','1','c3','40');
INSERT INTO test VALUES ('101','2','c1','30');
INSERT INTO test VALUES ('101','2','c2','80');
INSERT INTO test VALUES ('102','4','c1','9');
INSERT INTO test VALUES ('102','6','c2','50');
COMMIT;

方案一：經典 DECODE + 聚合函數（相容 8i 以上）

數值型 VAL 適用 SUM

SELECT dm1, dm2,
       SUM(DECODE(mc1, 'c1', val)) AS c1,
       SUM(DECODE(mc1, 'c2', val)) AS c2,
       SUM(DECODE(mc1, 'c3', val)) AS c3
FROM test
GROUP BY dm1, dm2;

輸出：

字串型 VAL 必須改用 MAX

若 VAL 含非數值字元，SUM 會報錯 ORA-01722。此時改用 MAX 同樣可行，因每組僅有一條非 NULL 值：

SELECT dm1, dm2,
       MAX(DECODE(mc1, 'c1', val)) AS c1,
       MAX(DECODE(mc1, 'c2', val)) AS c2,
       MAX(DECODE(mc1, 'c3', val)) AS c3
FROM test
GROUP BY dm1, dm2;

方案二：CASE WHEN 標準語法（可移植性更強）

SELECT dm1, dm2,
       MAX(CASE WHEN mc1 = 'c1' THEN val END) AS c1,
       MAX(CASE WHEN mc1 = 'c2' THEN val END) AS c2,
       MAX(CASE WHEN mc1 = 'c3' THEN val END) AS c3
FROM test
GROUP BY dm1, dm2;

邏輯等同 DECODE，符合 ANSI SQL 標準，跨資料庫遷移零修改。

方案三：Oracle 11g+ 專屬 PIVOT 子句（語意最清晰）

SELECT *
FROM test
PIVOT (
    MAX(val) FOR mc1 IN ('c1' AS c1, 'c2' AS c2, 'c3' AS c3)
)
ORDER BY dm1, dm2;

優勢：

• 語法直觀，自動產生欄位別名。
• 支援多聚合函數：PIVOT (SUM(val), COUNT(*) FOR ...)。
• 可搭配 XML 關鍵字動態產生欄位（適用於未知類別值）。

效能與選型建議

常見坑點提醒

1. VAL 型別不一：混存數值/字串時，統一用 MAX(TO_CHAR(...)) 或 MAX(TO_NUMBER(...)) 顯式轉型。
2. NULL 值顯示：報表層建議用 NVL(c1, 0) 或 COALESCE 填補空值。
3. 動態類別：若 MC1 值不固定，需動態 SQL 拼接或使用 PIVOT XML + XMLTABLE 解析。

掌握三種行轉列技巧，可根據版本限制與維護成本靈活選型，讓透視報表開發事半功倍。