orcale行转列，以“,”分割

orcale行转列，以“,”分割

转换前

select * from corp_financial_issue;

转换后

with temp0 as (select LEVEL lv from dual CONNECT BY LEVEL <= 100)
select issueid,substr(t.vals,instr(t.vals, ‘,’, 1, tv.lv) + 1,
instr(t.vals, ‘,’, 1, tv.lv + 1) -(
instr(t.vals, ‘,’, 1, tv.lv) + 1)
) AS salebranch_value
from (select issueid,’,’ || salebranch || ‘,’ AS vals,
length(salebranch || ‘,’) - nvl(length(REPLACE(salebranch, ‘,’)), 0) AS cnt
from corp_financial_issue) t join temp0 tv
on tv.lv <= t.cnt order by 1;