不用过程能否实现这个功能

[vber1010]

原表：
id item
1 60,65
1 87,35
2 40,68,70

要得到的查询
id item
1 60
1 65
1 87
1 35
2 40
2 68
2 70

[小灰狼W]

WITH A AS(SELECT 1 id,'60,65' item FROM dual
  UNION ALL SELECT 1,'87,35' FROM dual
  union all select 2,'40,68,70' from dual)
  
    select a.id,substr(a.item,instr(','||a.item,',',1,b.rn),instr(a.item||',',',',1,b.rn)-instr(','||a.item,',',1,b.rn))item
       from a,
       (select * from
          (SELECT DISTINCT id FROM A),
          (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM<=(
            select max(length(item)-length(replace(item,',')))+1 from a)))b
    WHERE A.ID=b.ID
      and length(a.item)-length(replace(a.item,',',''))+1>=b.rn

[lengyunfei006]

哎呀哇！

[iqlife]

不太可能....你要把字符串分解成列,首先...

[vber1010]

谢谢了！狂狼说的没错，之前我的问题的确和这个有相似地方，都怪我之前没去研究，只要了结果。非常感谢！

[ngx20080110]

另一种思路：

with tmp as
(

select 1 id, '60,65' item from dual
union all
select 1 id, '87,35' item from dual
union all
select 2 id, '40,68,70' item from dual
)
select distinct id, 
substr(regexp_substr(',' || item, ',([^,]+)', 1, level), 2) as "item"--, level,rownum
from tmp
connect by level <= length(regexp_replace(item, '[^,]', '')) + 1
order by id