oracle逗号隔开行转列_对逗号分隔的字符串进行行转列，详情请进，自带复杂解法，求简易方法...

9i下

--多行

with temp as

(select '6001600301,6001600302,6001600303,6001600304' text from dual

union

select '7001600301,7001600302,7001600303' text from dual

)

select substr(text,instr(text,',',1,rn)+1,instr(text,',',1,rn+1)-instr(text,',',1,rn)-1) text from

(

select ','||t1.text||',' text,t2.rn from

(select text,length(text)-length(replace(text,',',''))+1 rn from temp) t1,

(select rownum rn from all_objects where rownum <= (select max(length(text)-length(replace(text,',',''))+1) rn from temp)) t2

where t1.rn >= t2.rn order by text,rn

)

TEXT

6001600301

6001600302

6001600303

6001600304

7001600301

7001600302

7001600303

--单行

with temp as

(select '6001600301,6001600302,6001600303,6001600304' text from dual)

select substr(text,instr(text,',',1,rn)+1,instr(text,',',1,rn+1)-instr(text,',',1,rn)-1) text from

(

select ','||t1.text||',' text,t2.rn from temp t1,

(select rownum rn from all_objects where rownum <= (select length(text)-length(replace(text,',',''))+1 from temp)) t2

)

TEXT

6001600301

6001600302

6001600303

6001600304

10G下

--多行

with temp as

(select '6001600301,6001600302,6001600303,6001600304' text from dual

union

select '7001600301,7001600302,7001600303' text from dual

)

select regexp_substr(text,'[0-9]+',1,rn) text from

(

select t1.text,t2.rn from

(select text,length(text)-length(replace(text,',',''))+1 rn from temp) t1,

(select level rn from dual connect by rownum <= (select max(length(text)-length(replace(text,',',''))+1) rn from temp)) t2

where t1.rn >= t2.rn order by text,rn

)

TEXT

6001600301

6001600302

6001600303

6001600304

7001600301

7001600302

7001600303

--单行

with temp as

(select '6001600301,6001600302,6001600303,6001600304' text from dual)

select regexp_substr(text,'[0-9]+',1,rn) text from temp t1,

(select level rn from dual connect by rownum <= (select length(text)-length(replace(text,',',''))+1 from temp)) t2

TEXT

6001600301

6001600302

6001600303

6001600304