Oracle 行列轉置（PIVOT/UNPIVOT）實戰：從 DECODE 到 11g 原生語法完整演進

業務場景：銷售日報橫向展示

源數據（垂直表）：

目標報表（橫向表）：

方案一：經典 DECODE + GROUP BY（11g 前通用）

SELECT no,
       MAX(DECODE(day, 1, money)) AS mon,
       MAX(DECODE(day, 2, money)) AS tue,
       MAX(DECODE(day, 3, money)) AS thu
FROM   temp
GROUP BY no;

原理：DECODE 將非目標 DAY 轉為 NULL，MAX 忽略 NULL 保留目標值。缺點：需預知列值、擴展性差、可讀性低。

關鍵細節：DECODE 預設值陷阱

-- 錯誤：預設值 0 導致負值/NULL 變 0
MAX(DECODE(day, 1, money, 0))  -- 若 money=-45 則取 0！

正確：預設值用 NULL（省略或寫 NULL），MAX 會正確忽略。

方案二：CASE WHEN + GROUP BY（ANSI 標準，更清晰）

SELECT no,
       MAX(CASE WHEN day = 1 THEN money END) AS mon,
       MAX(CASE WHEN day = 2 THEN money END) AS tue,
       MAX(CASE WHEN day = 3 THEN money END) AS thu
FROM   temp
GROUP BY no;

支援複雜條件（如範圍判斷），可移植性強。

方案三：Oracle 11g+ 原生 PIVOT（最推薦）

SELECT * FROM (
    SELECT no, day, money FROM temp
)
PIVOT (
    MAX(money) FOR day IN (1 AS mon, 2 AS tue, 3 AS thu)
);

優勢：語法宣告式、自動生成列別名、效能優化器深度整合、可讀性極佳。

動態 PIVOT：列值不固定時的 PL/SQL 實現

CREATE OR REPLACE PROCEDURE gen_pivot_report(p_cursor OUT SYS_REFCURSOR) AS
    v_sql   VARCHAR2(4000);
    v_cols  VARCHAR2(4000);
BEGIN
    -- 1. 動態收集列值
    SELECT LISTAGG('''' || day || ''' AS day_' || day, ',') WITHIN GROUP (ORDER BY day)
    INTO   v_cols
    FROM   (SELECT DISTINCT day FROM temp);

    -- 2. 組裝動態 SQL
    v_sql := 'SELECT * FROM (SELECT no, day, money FROM temp)
              PIVOT (MAX(money) FOR day IN (' || v_cols || '))';

    -- 3. 開啟游標返回
    OPEN p_cursor FOR v_sql;
END;

UNPIVOT：橫表轉豎表（反向操作）

SELECT no, day, money
FROM   wide_table
UNPIVOT (
    money FOR day IN (mon AS 1, tue AS 2, thu AS 3)
);

適用於 ETL 規範化、報表數據源標準化。

效能與維護權衡

核心洞見

表結構穩定性 > 查詢便利性。垂直表（EAV 模式）新增 DAY 值只需 INSERT，無需 ALTER TABLE ADD COLUMN。PIVOT 只是展示層技術，不要為了報表方便而犧牲數據模型規範性。