行转列

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

来源： <http://www.itpub.net/thread-1145824-1-1.html>

 

来自为知笔记(Wiz)