오라클 버전 문제 같네요
listagg, regexp_count 11g에서만 되네요

10g 버전

WITH temp AS
(
SELECT 'A1|A2|A3|A4 , B1|B2|B3|B4 , C1|C2|C3|C4' str FROM dual
UNION ALL SELECT 'F1|F2|F3|F4 , G1|G2|G3|G4' FROM dual
UNION ALL SELECT 'H1|H2|H3|H4' FROM dual
UNION ALL SELECT null FROM dual
)
SELECT rn, str
     , WM_CONCAT(REGEXP_SUBSTR(str, '[^|]+', 1, 1)) v1
     , WM_CONCAT(REGEXP_SUBSTR(str, '[^|]+', 1, 2)) v2
  FROM (
      SELECT rn, str
                 , REGEXP_SUBSTR(str, '[^, ]+', 1, lv) v
                 , lv
              FROM (SELECT ROWNUM rn, str FROM temp)
                 , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99)
             WHERE lv(+) <= length(str) - length(replace(str, ',', ''))
      )
 GROUP BY rn, str