[Oracle SQL Debug] 解決Unpivot時，Null值被剔除的問題

* Finding the Problem

使用Unpivot時，發現Null值會被自動省略，造成資料不正確
SQL如下：

with unpivot_table as
(
    SELECT *
    FROM a
    UNPIVOT
    (
        qty
        FOR mm IN (MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6)
    )
),
temp_table as
(
    SELECT b.*, to_number(substr(b.mm, instr(b.mm, '_') + 1)) as seq
    FROM unpivot_table b
)
SELECT c.item, add_months(c.init_month, c.seq - 1) as mon, c.qty
FROM temp_table c;

原始資料如下：

轉換後資料如下：(2021/02/01的月份消失)

* Table Schema

CREATE TABLE a 
(
    item           varchar2(120),
    init_month     date,
    month_1        number,
    month_2        number,
    month_3        number
)

* 修正

增加INCLUDE NULLS關鍵字

with unpivot_table as
(
    SELECT *
    FROM a
    UNPIVOT INCLUDE NULLS
    (
        qty
        FOR mm IN (MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6)
    )
),
temp_table as
(
    SELECT b.*, to_number(substr(b.mm, instr(b.mm, '_') + 1)) as seq
    FROM unpivot_table b
)
SELECT c.item, add_months(c.init_month, c.seq - 1) as mon, c.qty
FROM temp_table c;

轉換後資料如下：