现遇到问题：
表中的记录为
ID name amount
1 a,b,c 10
转换成为
ID name amount
1 a 10
1 b 10
1 c 10
select * from aa ;
sql:
select id,
nvl(substr(name,instr(name,',',1,lvl)+1,instr(name,',',1,lvl+1)-instr(name,',',1,lvl)-1),' ') name,
amount
from (
select id,
lvl,
',' || name || ',' name,
amount
from aa a,
(select level lvl
from dual
connect by level <=
(select max(length(name) - length(replace(name, ','))) + 1
from aa)) b)
where substr(name,instr(name,',',1,lvl)+1,instr(name,',',1,lvl+1)-instr(name,',',1,lvl)-1) is not null
order by id,name
其中遇到通过 connect by 来构造数据
例如：
SQL> create table test as select level as id,level*10000 as sal from dual connect by level<=5;
表已创建。
SQL> select * from test;
ID SAL
---------- ----------
1 10000
2 20000
3 30000
4 40000
5 50000