oracle 分割 行转列,字符串分割行转列

with t(id,str) as(select 1,'11,12,13,14' from dual union all select 2,'21,22,23' from dual)

方法1，

select id,regexp_substr(str,'[^,]+',1,level) str

from t

connect by level<=regexp_count(str,'[^,]+')

and prior id=id

and prior sys_guid() is not null;

方法2，

select regexp_substr(t.str,'[^,]+',1,v.l)

from t,

(select level l from dual connect by level<=100) v

where v.l<=regexp_count(t.str,'[^,]+');

方法3，

select id,regexp_substr(str,'[^,]+',1,column_value) str

from t,

table(cast(multiset(select level from dual connect by level<=regexp_count(str,'[^,]+')) as sys.odcivarchar2list));

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30363279/viewspace-2127935/，如需转载，请注明出处，否则将追究法律责任。