oracle 一行变多行

1、正则表达式

SELECT distinct T.MY_ID, REGEXP_SUBSTR(T.MY_VALUE, '[^,]', 1, LEVEL)  --返回第level次匹配的结果
FROM MY_CHANGE T
CONNECT BY LEVEL <= REGEXP_COUNT(T.MY_VALUE, ',') + 1    --循环次数
ORDER BY T.MY_ID;



2、用connct by rownum 和substr

select my_id, substr(replace(my_value,',',''),tb2.rn,1)sub from
MY_CHANGE ,(select rownum rn from dual connect by rownum<4) tb2    
where  substr(regexp_replace(my_value,',',''),tb2.rn,1) is not null
ORDER BY MY_ID;