比如某一个字段值是:a,b,c 想将这个转换成3行,另外其他保持不变
例: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也就是说 除了第一个之外,其他的都没有成功转换? 求教高手指点
例: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也就是说 除了第一个之外,其他的都没有成功转换? 求教高手指点
with a as (select 'a,b,c,d' || ',' as id from dual)
select regexp_substr(id,'[^,]+',1,rownum) as id from a
connect by rownum <= length(regexp_replace(id,'[^,]+'));
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' as type, 'test2' as t1, 'test2' as t2, 'test2' as t3 from dual union all
select '3' as id, 'b' as type, 'test3' as t1, 'test3' as t2, 'test3' as t3 from dual union all
select '4' as id, 'c' as type, 'test4' as t1, 'test4' as t2, 'test4' as t3 from dual
)
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(regexp_replace(type,'[^,]+'));
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;
看了好久也没找到相应的别名 求教