根据,为依据进行拆分
原数据为
A 1,2,3
B 1,66,88
最后得到的数据为:
A 1
A 2
A 3
B 1
B 66
B 88
如果是10G及以前的版本,可以用如下SQL:
with t as (
select 'A' id,'1,2,3' data from dual union all
select 'B' id,'1,66,88' data from dual)
select id,regexp_substr(data,'[^,]+',1,level) data from t connect by prior id=id and level<=length(data)-length(regexp_replace(data,',',''))+1 and prior dbms_random.value is not null;
如果是11g及以后的版本,可以用如下的SQL:
with t as (
select 'A' id,'1,2,3' data from dual union all
select 'B' id,'1,66,88' data from dual)
select id,regexp_substr(data,'[^,]+',1,level) data from t connect by prior id=id and level<=regexp_count(data,',')+1 and prior dbms_random.value is not null;