WITH d AS (
SELECT 1 id,'01,02,03,04' months,10000 total_amount FROM DUAL UNION ALL
SELECT 2 id,'01,02'
,4000 FROM DUAL UNION ALL
SELECT 3 id,'05,06'
,5000 FROM DUAL
)
SELECT id,REGEXP_SUBSTR(months,'[^,]+',1,rn) m, total_amount/cnt
FROM (SELECT d.*,LENGTH(months)-LENGTH(REPLACE(months,','))+1 cnt
FROM d
)
,(SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=100)
WHERE rn<=cnt
ORDER BY 1,2;
|
