---构建测试表t如下:
select 'a1' x, 'a,b,c,d' y from dual
union all
select 'a2' x, 'e,f,g' y from dual
---需求:依据上表中y字段的值,将一行记录拆分为多行,并达到如下效果
--方法1---
with t AS (
select 'a1' x, 'a,b,c,d' y from dual
union all
select 'a2' x, 'e,f,g' y from dual
)
SELECT y, regexp_substr(y||',', '[^,]+', 1, rownum) yy
from (select y from t
WHERE y like '%,%'
) connect by rownum <= length(y) - length(replace(y, ',', ''))+1
该语句的执行结果如下: