字符串分割行转列

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30363279/viewspace-2127935/