Oracle SQl: convert several commas string into rows

I’ve this table (Holidays):

CCAA     FREEDAYS
AND       01/01,01/03
MAD       01/01,03/03
EUS       01/01,31/12
    ....

and i want to obtein this other table:

CCAA     FREEDAY
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