By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TABLE_NAME ( value ) AS
SELECT 'a,b,c,d,e' FROM DUAL;
1 rows affected
SELECT t.value,
v.COLUMN_VALUE AS item
FROM table_name t
CROSS JOIN TABLE(
CAST(
MULTISET(
SELECT REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL )
FROM DUAL
CONNECT BY REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL ) IS NOT NULL
)
AS SYS.ODCIVARCHAR2LIST
)
) v
| VALUE | ITEM |
|---|---|
| a,b,c,d,e | a |
| a,b,c,d,e | b |
| a,b,c,d,e | c |
| a,b,c,d,e | d |
| a,b,c,d,e | e |