rt,上次在各位大侠的热情帮助下解决了多行转化成字符串的问题,这次又碰到麻烦了
id1,2,4,5,6,8怎么把它转化成
id details
1 1
2 2
3 4
4 5
5 6
6 8简单的说就是怎么把字符串拆成多行,csdn是个神奇的地方,还请各位大侠帮帮忙,指点迷津
id1,2,4,5,6,8怎么把它转化成
id details
1 1
2 2
3 4
4 5
5 6
6 8简单的说就是怎么把字符串拆成多行,csdn是个神奇的地方,还请各位大侠帮帮忙,指点迷津
调试欢乐多
from tt
connect by level<=length(a)-length(replace(a,','))+1LEVEL A
1 1
2 2
3 4
4 5
5 6
6 8
http://topic.csdn.net/u/20091023/17/CF3F75D1-DE50-4EE1-B9BE-43E4AF3C62EF.htmlrownum<=后面的数字根据需要来设,要大于字符串中逗号数的最大值
2 select rownum id, substr(a,rownum*2-1,1) details from tt
3 connect by rownum<=(length(a)+1)/2
4 ;
ID DETAILS
---------- -------
1 1
2 2
3 4
4 5
5 6
6 8
6 rows selected
都是一位数的话是可以的 有个两位数就不行了
SQL> with tt as(select '1,2,4,55,6,8' a from dual)
2 select rownum id, substr(a,rownum*2-1,1) details from tt
3 connect by rownum <=(length(a)+1)/2; ID DET
---------- ---
1 1
2 2
3 4
4 5
5 ,
6 ,6 rows selected.
with部分去掉,那只是用来生成测试数据的
个数不固定,只能写个函数来实现了。得到一个index by表,然后再返回一个游标。。
select b.rn id,substr(a.id,instr(','||a.id,',',1,b.rn),instr(a.id||',',',',1,b.rn)-instr(','||a.id,',',1,b.rn))details
from a,
(select rownum rn from dual connect by rownum<100)b
where length(a.id)-length(replace(a.id,',',''))+1>=b.rn
看看是否可以。没好好看代码,还不虚心请教,多等了2天,唉
with tt as(select '1,2,4,5,6,8' name from dual) select
substr(name,b.id,instr(a.name||',',',',b.id)-b.id) as name
from tt a,(select rownum as id from dual connect by rownum<=100 ) b
where substr(','||a.name,b.id,1)=','
with tt as(select '1,2,4,5,6,8' name from dual) select row_number()over(order by b.id) as num,
substr(name,b.id,instr(a.name||',',',',b.id)-b.id) as name
from tt a,(select rownum as id from dual connect by rownum<=100 ) b
where substr(','||a.name,b.id,1)=','
with tt as(select '1,200,44,1115,6,8' name from dual) select row_number()over(order by b.id) as num,
substr(name,b.id,instr(a.name||',',',',b.id)-b.id) as name
from tt a,(select rownum as id from dual connect by rownum<=100 ) b
where substr(','||a.name,b.id,1)=','
id type xxx xxx xxx
1 a,b,c test1 test1 test1
2 a test2 test2 test2
3 b test3 test3 test3
4 c test4 test4 test4
比如A表有这些数据,
我用上面的方法拆分后,本来想得到的数据是:
1 a test1 test1 test1
1 b test1 test1 test1
1 c test1 test1 test1
2 a test2 test2 test2
3 b test3 test3 test3
4 c test4 test4 test4
但是现在得到的却是:
1 a test1 test1 test1
1 b test1 test1 test1
1 c test1 test1 test1
2 test2 test2 test2
3 test3 test3 test3
4 test4 test4 test4也就是说 除了第一个之外,其他的都没有成功转换? 求教高手指点