Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

2 rows affected

WITH data ( vala, valb, valc, starta, startb, startc, enda, endb, endc, rn, idx ) AS (
  SELECT vala,
         valb,
         valc,
         1,
         1,
         1,
         INSTR( vala, ',', 1 ),
         INSTR( valb, ',', 1 ),
         INSTR( valc, ',', 1 ),
         ROWNUM,
         1
  FROM   testdata
UNION ALL
  SELECT vala,
         valb,
         valc,
         CASE WHEN enda = 0 THEN 0 ELSE enda + 1 END,
         CASE WHEN endb = 0 THEN 0 ELSE endb + 1 END,
         CASE WHEN endc = 0 THEN 0 ELSE endc + 1 END,
         CASE WHEN enda = 0 THEN 0 ELSE INSTR( vala, ',', enda + 1 ) END,
         CASE WHEN endb = 0 THEN 0 ELSE INSTR( valb, ',', endb + 1 ) END,
         CASE WHEN endc = 0 THEN 0 ELSE INSTR( valc, ',', endc + 1 ) END,
         rn,
         idx + 1
  FROM   data
  WHERE  enda > 0
  OR     endb > 0
  OR     endc > 0
)
SELECT CASE
       WHEN starta = 0 THEN NULL
       WHEN enda   = 0 THEN SUBSTR( vala, starta )
                       ELSE SUBSTR( vala, starta, enda - starta )
       END AS vala,
       CASE
       WHEN startb = 0 THEN NULL
       WHEN endb   = 0 THEN SUBSTR( valb, startb )
                       ELSE SUBSTR( valb, startb, endb - startb )
       END AS valb,
       CASE
       WHEN startc = 0 THEN NULL
       WHEN endc   = 0 THEN SUBSTR( valc, startc )
                       ELSE SUBSTR( valc, startc, endc - startc )
       END AS valc
FROM   data
ORDER BY rn, idx;

VALA	VALB	VALC
1	5	9
2	6	10
3	7	11
4	8	null
12	13	null
null	14	null
null	15	null
null	16	null