Convert several commas string into rows

Question

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 obtain 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 obtain a table with endless rows...

Can you help me, please? Thanks a lot.

Answer 1

You need the connect-by clause to link back to the same CCAA value; but as that introduces loops you also need to include a non-deterministic function. (There's a good explanation of this process in this Oracle Community post). I'm using dbms_random.value, by you could use sys_guid(), etc.

...
connect by REGEXP_SUBSTR (txt, '[^,]+', 1, level) is not null
and prior ccaa = ccaa
and prior dbms_random.value is not null;

Not sure why you have a CTE here though, as it doesn't seem to be adding anything:

select REGEXP_SUBSTR (freedays, '[^,]+', 1, level) as freeday, CCAA
from holidays
connect by REGEXP_SUBSTR (freedays, '[^,]+', 1, level) is not null
and prior ccaa = ccaa
and prior dbms_random.value is not null;

FREEDAY     CCA
----------- ---
01/01       AND
01/03       AND
01/01       EUS
31/12       EUS
01/01       MAD
03/03       MAD

 6 rows selected