2020年3月17日 星期二

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

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
最後,再利用函數substrinstr搭配:
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

沒有留言:

張貼留言