SELECT t.a1,
        trim(regexp_substr(t.a2, '[^,]+', 1, lines.column_value)) text
    FROM test t,
      TABLE (CAST (MULTISET
      (SELECT LEVEL FROM dual
              CONNECT BY instr(t.a2, ',', 1, LEVEL - 1) > 0
      ) AS sys.odciNumberList ) ) lines
    ORDER BY a1, lines.column_value;

CREATE TABLE TEST
   (	A1 VARCHAR2(10 BYTE), 
	A2 VARCHAR2(1000 BYTE)
   );

insert into test values('x','1,2,3,4');
insert into test values('y','a,b,c');
insert into test values('z','11,12,13,14');

o/p:

x     y      z
---  ---    ---
1     a      11
2     b      12
3     c      13
4

SQL> SELECT t.a1, lines.column_value,
  2          trim(regexp_substr(t.a2, '[^,]+', 1, lines.column_value)) text
  3      FROM test t,
  4        TABLE (CAST (MULTISET
  5        (SELECT LEVEL FROM dual
  6                CONNECT BY instr(t.a2, ',', 1, LEVEL - 1) > 0
  7        ) AS sys.odciNumberList ) ) lines
  8      ORDER BY a1, lines.column_value;
A1         COLUMN_VALUE TEXT
---------- ------------ --------------------
x                     1 1
x                     2 2
x                     3 3
x                     4 4
y                     1 a
y                     2 b
y                     3 c
z                     1 11
z                     2 12
z                     3 13
z                     4 14

SQL> select max(decode(a1, 'x', text)) x,
  2         max(decode(a1, 'y', text)) y,
  3         max(decode(a1, 'z', text)) z
  4  from (
  5  SELECT t.a1, lines.column_value,
  6          trim(regexp_substr(t.a2, '[^,]+', 1, lines.column_value)) text
  7      FROM test t,
  8        TABLE (CAST (MULTISET
  9        (SELECT LEVEL FROM dual
 10                CONNECT BY instr(t.a2, ',', 1, LEVEL - 1) > 0
 11        ) AS sys.odciNumberList ) ) lines
 12  )
 13  group by column_value
 14  ORDER BY column_value;
X                    Y                    Z
-------------------- -------------------- --------------------
1                    a                    11
2                    b                    12
3                    c                    13
4                                         14

SCOTT@orcl_12.1.0.2.0> VARIABLE g_ref REFCURSOR
SCOTT@orcl_12.1.0.2.0> DECLARE
  2    v_sql  VARCHAR2(32767);
  3  BEGIN
  4    v_sql := 'SELECT ';
  5    FOR i IN
  6  	 (SELECT DISTINCT a1
  7  	  FROM	 test
  8  	  ORDER  BY a1)
  9    LOOP
 10  	 v_sql := v_sql || 'MAX(DECODE(a1,''' || i.a1 || ''',text)) ' || i.a1 || ',';
 11    END LOOP;
 12    v_sql := RTRIM (v_sql, ',') ||
 13  	 ' FROM  (SELECT t.a1, lines.COLUMN_VALUE,
 14  			 TRIM (REGEXP_SUBSTR (t.a2, ''[^,]+'', 1, lines.COLUMN_VALUE)) text
 15  		  FROM	 test t,
 16  			 TABLE
 17  			   (CAST
 18  			     (MULTISET
 19  			       (SELECT	LEVEL
 20  				FROM	DUAL
 21  				CONNECT BY INSTR (t.a2, '','', 1, LEVEL - 1) > 0)
 22  			    AS SYS.ODCINUMBERLIST)) lines)
 23  	   GROUP  BY COLUMN_VALUE
 24  	   ORDER  BY COLUMN_VALUE';
 25    OPEN :g_ref FOR v_sql;
 26  END;
 27  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_ref

X          Y          Z
---------- ---------- ----------
1          a          11
2          b          12
3          c          13
4                     14

4 rows selected.