请教 如何将某一个字段中的字符串转换成行
比如某一个字段值是:a,b,c 想将这个转换成3行，另外其他保持不变
例：

SQL code

A表
id   type    xxx   xxx   xxx  
1   a,b,c   test1 test1 test1
2    a,b     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
2 b 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
2   test2 test2 test2
3   test3 test3 test3
4   test4 test4 test4

也就是说 除了第一个之外，其他的都没有成功转换？ 求教高手指点

------解决方案--------------------
前面的sql有点问题，这个应该能满足你的要求
with a as (
select '1' as id, 'a,b,c' as type, 'test1' as t1, 'test1' as t2, 'test1' as t3 from dual union all
select '2' as id, 'a,b,c,d' as type, 'test2' as t1, 'test2' as t2, 'test2' as t3 from dual union all
select '3' as id, 'b,c,d' as type, 'test3' as t1, 'test3' as t2, 'test3' as t3 from dual union all
select '4' as id, 'c,d' as type, 'test4' as t1, 'test4' as t2, 'test4' as t3 from dual
)
select * from (
select id,regexp_substr(type,'[^,]+',1,(rownum-nvl((select length(replace(wm_concat(type),',','')) from a b where b.id<a.id),0))
) as type,t1,t2,t3 from a
connect by (rownum-nvl((select length(replace(wm_concat(type),',','')) from a b where b.id<a.id),0)) 
<=length(replace(type,',',''))
) where type is not null;