Oracle SQl: convert several commas string into rows

I’ve this table (Holidays):

AND       01/01,01/03
MAD       01/01,03/03
EUS       01/01,31/12

and i want to obtein this other table:

AND       01/01
AND       01/03
MAD       01/01
MAD       03/03
EUS       01/01
EUS       31/12

I’m using this sql query:

with t as (SELECT freedays AS txt, CCAA AS CCAA
          FROM HOLIDAYS )

select REGEXP_SUBSTR (txt, '[^,]+', 1, level) as freeday, CCAA
from t
connect by REGEXP_SUBSTR (txt, '[^,]+', 1, level) is not null

But i obtein a table with endless rows…

can you help me, please?. Thanks a lot.

Source: oracle

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.