Oracle 一行转多行(一)

一.一行转多行

使用如下语句拆分:

select DISTINCT regexp_substr(you_col,'[^,]+',1,level)a,t.* 
from you_table t 
connect by level<=length(you_col)-length(regexp_replace(you_col,',',''))+1 
order by sort

二.进阶(1)

使用如下语句拆分:

select DISTINCT regexp_substr(you_col,'[^,]+',1,level)a,regexp_substr(you_col2,'[^,]+',1,level)b,t.* 
from you_table  t 
connect by level<=length(you_col)-length(regexp_replace(you_col,',',''))+1 order by a

三.进阶(2)

使用如下语句拆分:

select DISTINCT a,regexp_substr(you_col2,'[^,]+',1,level)b,you_col,sort from 
(select DISTINCT regexp_substr(you_col,'[^,]+',1,level)a,t.* 
from you_table t 
connect by level<=length(you_col)-length(regexp_replace(you_col,',',''))+1 )t1
connect by level<=length(you_col2)-length(regexp_replace(you_col2,',',''))+1 
order by a,b

详情(lee_chauncy的<oracle列转行以及一行转多行>)