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


Posted by Vanchy on 2021-08-06

* 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;

原始資料如下:

ITEM INIT_MONTH MONTH_1 MONTH_2 MONTH_3
Apple 2021/01/01 5 7

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

ITEM MON QTY
Apple 2021/01/01 5
Apple 2021/03/01 7

* 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;

轉換後資料如下:

ITEM MON QTY
Apple 2021/01/01 5
Apple 2021/02/01
Apple 2021/03/01 7

#oracle #Oracle SQL







Related Posts

Linear Kalman Filter 筆記

Linear Kalman Filter 筆記

原子習慣:進階策略 - 如何從「A」到「A+」

原子習慣:進階策略 - 如何從「A」到「A+」

矽谷機器人公司一覽 & 相關資源

矽谷機器人公司一覽 & 相關資源


Comments