Oracle_將一欄內的多筆資料拆解

tags: oracle 欄位拆解

說明

平常可能遇到前端寫入的資料是一個欄位內有多筆的資料，也就是一個cell內的記錄是長這樣子：

column_A	column_B
A	1,2,3,4,5,6,7

而使用者希望呈現的時候是：

column_A	column_B
A	1
A	2
A	3
A	4
A	5
A	6
A	7

作法

首先，弄出假資料：

select 'A' column_a, '1,2,3,4,5,6,7' column_b from dual

接著，將column_b的前後都加上一個,，並計算內含的資料筆數：

select column_a, 
    ',' || column_b || ',' column_b, -- 頭尾加上,
 length(column_b || ',') - length(replace(column_b, ',')) dc-- 計算裡面的資料筆數
 from (
select 'A' column_a, '1,2,3,4,5,6,7' column_b from dual
)a

接著，利用connect by level設置一個範圍內的長度欄位：

select * 
  from (
 select column_a, 
  ',' || column_b || ',' column_b, -- 頭尾加上,
  length(column_b || ',') - length(replace(column_b, ',')) dc -- 計算裡面的資料筆數
  from (
             select 'A' column_a, '1,2,3,4,5,6,7' column_b from dual
         )a
  )b,
  -- 設置一個長度區間<=20的欄位，這部份是依實際需求所設置的長度
  (select level lv from dual connect by level <=20) c 
  where c.lv <= b.dc -- 要注意，是<=，這樣就能一口氣串出所有的資料

這時候的資料是長這樣的：

COLUMN_A	COLUMN_B	DC	LV
A	,1,2,3,4,5,6,7,	7	1
A	,1,2,3,4,5,6,7,	7	2
A	,1,2,3,4,5,6,7,	7	3
A	,1,2,3,4,5,6,7,	7	4
A	,1,2,3,4,5,6,7,	7	5
A	,1,2,3,4,5,6,7,	7	6
A	,1,2,3,4,5,6,7,	7	7

最後，再利用函數substr與instr搭配：

select b.*, 
  substr(b.column_b, -- 取值的字串來源
         instr(b.column_b, ',', 1, c.lv) + 1, -- 從那開始，instr是取索引值所在位置，意思就是從b.column_b的第1個字元開始找','，然後取第c.lv個
         instr(b.column_b, ',', 1, c.lv + 1) - (instr(b.column_b, ',', 1, c.lv) + 1)) split_data -- 到那裡
  from (
    select column_a, 
        ',' || column_b || ',' column_b, -- 頭尾加上,
        length(column_b || ',') - length(replace(column_b, ',')) dc -- 計算裡面的資料筆數
        from (
              select 'A' column_a, '1,2,3,4,5,6,7' column_b from dual
        )a
  )b,
  -- 設置一個長度區間<=20的欄位，這部份是依實際需求所設置的長度，以這次的案例設置10也可以
  (select level lv from dual connect by level <=20) c 
  where c.lv <= b.dc -- 要注意，是<=，這樣就能一口氣串出所有的資料

成功將一個欄位的資料切割為多欄，如下：

COLUMN_A	COLUMN_B	DC	SPLIT_DATA
A	,1,2,3,4,5,6,7,	7	1
A	,1,2,3,4,5,6,7,	7	2
A	,1,2,3,4,5,6,7,	7	3
A	,1,2,3,4,5,6,7,	7	4
A	,1,2,3,4,5,6,7,	7	5
A	,1,2,3,4,5,6,7,	7	6
A	,1,2,3,4,5,6,7,	7	7

20200320.
後來發現有更簡單的方法
select regexp_substr('1,2,3,4,5,6,7','[^,]+', 1, level) aufnr from dual <\br> connect by regexp_substr('1,2,3,4,5,6,7', '[^,]+', 1, level) is not null

reference: