关于字符串转行解决思路

关于字符串转行
rt，上次在各位大侠的热情帮助下解决了多行转化成字符串的问题，这次又碰到麻烦了 
  id

1,2,4,5,6,8

怎么把它转化成
id details
1 1
2 2
3 4
4 5
5 6
6 8

简单的说就是怎么把字符串拆成多行，****是个神奇的地方，还请各位大侠帮帮忙，指点迷津

------解决方案--------------------
with tt as(select '1,2,4,5,6,8' a from dual)

select level,substr(a,instr(','||a,',',1,level),instr(a||',',',',1,level)-instr(','||a,',',1,level))a
from tt
connect by level<=length(a)-length(replace(a,','))+1

LEVEL A
1 1
2 2
3 4
4 5
5 6
6 8
------解决方案--------------------
多个字符串的话参考
http://topic.****.net/u/20091023/17/CF3F75D1-DE50-4EE1-B9BE-43E4AF3C62EF.html

rownum<=后面的数字根据需要来设，要大于字符串中逗号数的最大值
------解决方案--------------------

探讨

with tt as(select '1,2,4,5,6,8' a from dual)

select level,substr(a,instr(','||a,',',1,level),instr(a||',',',',1,level)-instr(','||a,',',1,level))a
from tt
connect by level <=length(a)-length(replace(a,','))+1

LEVEL A
1 1
2 2
3 4
4 5
5 6
6 8

------解决方案--------------------
SQL> with tt as(select '1,2,4,5,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
4 ;
 
ID DETAILS
---------- -------
1 1
2 2
3 4
4 5
5 6
6 8
 
6 rows selected
------解决方案--------------------

探讨

with tt as(select '1,2,4,5,6,8' a from dual)

select level,substr(a,instr(','||a,',',1,level),instr(a||',',',',1,level)-instr(','||a,',',1,level))a
from tt
connect by level <=length(a)-length(replace(a,','))+1

LEVEL A
1 1
2 2
3 4
4 5
5 6
6 8

------解决方案--------------------

探讨

SQL> with tt as(select '1,2,4,5,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
  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,5,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
  4  ;

        ID DETAILS
---------- -------
        1 1
        2 2
        3 4
        4 5
        5 6
        6 8

6 rows selected
呵呵 你这个有点瑕疵
都是一位数的话是可以的 有个两位数就不行了
SQL codeSQL>with ttas(select'1,2,4,55,6,8' afrom dual)2select rownum id, substr(a,rownum*2-1,1) detailsfrom tt3 connectby rownum<=(length(a)+1)/2;

ID DET---------- ---112234455 ,6 ,6 rows selected.

------解决方案--------------------