只需設定分隔符，完全自適應行轉列~~

呵呵，又來一個行轉列，難道我跟行轉列卯上了？聽說11g中有了更簡單的法子來實現行列轉換，黑黑，說不得以後還得再來一篇啊。

create table tmp5 (id number(2),name varchar2(10),code varchar2(50));
alter table tmp5 add constraint pk_tmp5 primary key (id);
insert into tmp5 values (1,'aaa','c001/c002/c007');
insert into tmp5 values (2,'bbb','c001/c003');
insert into tmp5 values (3,'ccc','c008/c0011/c029/c023');
insert into tmp5 values (4,'ddd','c102/c111/c112/c144/c167');

select id,
name,
--rn,
--code,
substr(code, st, decode(ed, 0, length(code), ed - st)) code
from (select id,
name,
code,
rn,
decode(rn, 1, 1, instr(code, '/', 1, rn - 1) + 1) st,
instr(code, '/', 1, rn) ed
from (select *
from tmp5,
(select rownum rn
from dual
connect by rownum <=
(select max(length(code)) -
max(length(replace(code, '/', ''))) + 1
from tmp5))))
where ed > 0
or st > 1
order by id, rn;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7607759/viewspace-22286/，如需轉載，請註明出處，否則將追究法律責任。