SCOTT@orcl_12.1.0.2.0> SELECT * FROM your_table
  2  /

NAME          ID S
----- ---------- -
TEST         123 A
TEST         123 B
TEST         123 C
TEST1        123 D
TEST1        123 E
TEST1        123 F
TEST1        123 G

7 rows selected.

SCOTT@orcl_12.1.0.2.0> VARIABLE g_ref REFCURSOR
SCOTT@orcl_12.1.0.2.0> DECLARE
  2    v_size  NUMBER;
  3    v_col   NUMBER := 0;
  4    v_sql   VARCHAR2(32767);
  5  BEGIN
  6    SELECT GREATEST (MAX (LENGTH (subject)), LENGTH (COUNT (DISTINCT subject)) + 7)
  7    INTO   v_size
  8    FROM your_table;
  9    v_sql := 'SELECT name,id';
 10    FOR r IN (SELECT DISTINCT subject FROM your_table ORDER BY subject) LOOP
 11  	 v_col := v_col + 1;
 12  	 v_sql := v_sql||',CAST(MAX(DECODE(subject,'''||r.subject||''',subject)) AS VARCHAR2('
 13  	       ||v_size||')) "SUBJECT'||v_col||'"';
 14    END LOOP;
 15    v_sql := v_sql || ' FROM your_table GROUP BY name, id ORDER BY 1, 2, 3';
 16    OPEN :g_ref FOR v_sql;
 17  END;
 18  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_ref

NAME          ID SUBJECT1 SUBJECT2 SUBJECT3 SUBJECT4 SUBJECT5 SUBJECT6 SUBJECT7
----- ---------- -------- -------- -------- -------- -------- -------- --------
TEST         123 A        B        C
TEST1        123                            D        E        F        G

2 rows selected.

create table example1 
(
name varchar2(10),
grade varchar2(10)
);

INSERT ALL
   INTO example1 (name, grade) VALUES ('ABC', 'A')
   INTO example1 (name, grade) VALUES ('ABC', 'B')
   INTO example1 (name, grade) VALUES ('ABC', 'C')
   INTO example1 (name, grade) VALUES ('ABC', 'D')
   INTO example1 (name, grade) VALUES ('DEF', 'E')
   INTO example1 (name, grade) VALUES ('DEF', 'F')
   INTO example1 (name, grade) VALUES ('DEF', 'G')
SELECT 1 FROM DUAL;

select * from example1;

NAME       GRADE    
---------- ----------
ABC        A          
ABC        B          
ABC        C          
ABC        D          
DEF        E          
DEF        F          
DEF        G          

 7 rows selected 
 

create table example2
(
name varchar2(10),
grade1  varchar2(10),
grade2  varchar2(10),
grade3  varchar2(10),
grade4  varchar2(10),
grade5  varchar2(10),
grade6  varchar2(10),
grade7  varchar2(10)
);

And finally the result to be inserted into example2 table and the result should looks like:

select * from example2;

NAME   GRADE1    GRADE2   GRADE3    GRADE4    GRADE5    GRAD6    GRAD7    
---------- ---------------------------------------------------------------------
ABC    A          B       C         D         null      null      null    
DEF    E          F       G         null      null      null      null 

SCOTT@orcl_12.1.0.2.0> select * from example1
  2  /

NAME       GRADE
---------- ----------
ABC        A
ABC        B
ABC        C
ABC        D
DEF        E
DEF        F
DEF        G

7 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from example2
  2  /

no rows selected

SCOTT@orcl_12.1.0.2.0> INSERT INTO example2 (name, grade1, grade2, grade3, grade4, grade5, grade6, grade7)
  2  SELECT name,
  3  	    MAX (DECODE (dr, 1, grade)),
  4  	    MAX (DECODE (dr, 2, grade)),
  5  	    MAX (DECODE (dr, 3, grade)),
  6  	    MAX (DECODE (dr, 4, grade)),
  7  	    MAX (DECODE (dr, 5, grade)),
  8  	    MAX (DECODE (dr, 6, grade)),
  9  	    MAX (DECODE (dr, 7, grade))
 10  FROM   (SELECT name, grade, DENSE_RANK () OVER (PARTITION BY name ORDER BY grade) dr
 11  	     FROM   example1)
 12  GROUP  BY name
 13  /

2 rows created.

SCOTT@orcl_12.1.0.2.0> set null null
SCOTT@orcl_12.1.0.2.0> select * from example2
  2  /

NAME       GRADE1     GRADE2     GRADE3     GRADE4     GRADE5     GRADE6     GRADE7
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ABC        A          B          C          D          null       null       null
DEF        E          F          G          null       null       null       null

2 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from example1
  2  /

NAME       GRADE
---------- ----------
ABC        A
ABC        B
ABC        C
ABC        D
DEF        E
DEF        F
DEF        G

7 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from example2
  2  /
select * from example2
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SCOTT@orcl_12.1.0.2.0> DECLARE
  2    v_grades  NUMBER;
  3    v_sql	 VARCHAR2(32767);
  4  BEGIN
  5    SELECT COUNT (DISTINCT grade) INTO v_grades FROM example1;
  6    v_sql := 'CREATE TABLE example2 AS SELECT name';
  7    FOR i IN 1..v_grades LOOP
  8  	 v_sql := v_sql || ',MAX(DECODE(dr,' || i || ',grade)) grade' || i;
  9    END LOOP;
 10    v_sql := v_sql || ' FROM (SELECT name,grade,DENSE_RANK()OVER (PARTITION BY name ORDER BY grade) dr
 11  		FROM example1) GROUP BY name';
 12    EXECUTE IMMEDIATE v_sql;
 13  END;
 14  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> set null null
SCOTT@orcl_12.1.0.2.0> select * from example2 order by 1
  2  /

NAME       GRADE1     GRADE2     GRADE3     GRADE4     GRADE5     GRADE6     GRADE7
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ABC        A          B          C          D          null       null       null
DEF        E          F          G          null       null       null       null

2 rows selected.

VARIABLE g_ref REFCURSOR
DECLARE
    v_size  NUMBER;
    v_col   NUMBER := 0;
    v_sql   VARCHAR2(32767);
    v_sql1   VARCHAR2(32767);
  BEGIN
    SELECT LENGTH (COUNT (DISTINCT grade)) + 1000 INTO v_size FROM example1;
--    DBMS_OUTPUT.PUT_LINE ('v_size IS:'||v_size);
    v_sql := ' SELECT name';
    v_sql1 := 'INSERT INTO EXAMPLE2 (name';
--    FOR r IN (SELECT grade FROM example1 order by grade) LOOP
     FOR r IN (SELECT name, grade, ROW_NUMBER () OVER (PARTITION BY name ORDER BY name) dr FROM example1) LOOP 
  	 v_col := v_col + 1;
     
     v_sql := v_sql||',CAST(MAX(DECODE(dr,'||v_col||',grade)) AS VARCHAR2('
  	       ||v_size||')) "grade'||v_col||'"'; 
           
     v_sql1 := v_sql1||', grade'||v_col||''; 
           
--    DBMS_OUTPUT.PUT_LINE ('v_sql IS:'||v_sql);    
--    DBMS_OUTPUT.PUT_LINE ('v_sql1 IS:'||v_sql1);
   
    END LOOP;
    v_sql := v_sql || ' FROM (SELECT name, grade, ROW_NUMBER () OVER (PARTITION BY name ORDER BY name) dr FROM example1) GROUP BY name';
    v_sql1 := v_sql1 || ')' || v_sql ;
    --DBMS_OUTPUT.PUT_LINE ('v_sql IS:'||v_sql); 
    DBMS_OUTPUT.PUT_LINE ('v_sql1 IS:'||v_sql1); 
    OPEN :g_ref FOR v_sql;
--    OPEN :g_ref FOR v_sql1;
  END;
  /

SCOTT@orcl_12.1.0.2.0> DESC example1
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 NAME                                                           VARCHAR2(10)
 GRADE                                                          VARCHAR2(10)

SCOTT@orcl_12.1.0.2.0> SELECT * FROM example1
  2  /

NAME       GRADE
---------- ----------
ABC        A
ABC        B
ABC        C
ABC        D
DEF        E
DEF        F
DEF        G

7 rows selected.

SCOTT@orcl_12.1.0.2.0> DESC example2
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 NAME                                                           VARCHAR2(10)
 GRADE1                                                         VARCHAR2(10)
 GRADE2                                                         VARCHAR2(10)
 GRADE3                                                         VARCHAR2(10)
 GRADE4                                                         VARCHAR2(10)
 GRADE5                                                         VARCHAR2(10)
 GRADE6                                                         VARCHAR2(10)
 GRADE7                                                         VARCHAR2(10)

SCOTT@orcl_12.1.0.2.0> SELECT * FROM example2
  2  /

no rows selected

SCOTT@orcl_12.1.0.2.0> SET SERVEROUTPUT ON SIZE 1000000
SCOTT@orcl_12.1.0.2.0> DECLARE
  2    v_grades  NUMBER;
  3    v_sql1	 VARCHAR2(32767);
  4    v_sql2	 VARCHAR2(32767);
  5  BEGIN
  6    SELECT COUNT (DISTINCT grade) INTO v_grades FROM example1;
  7    v_sql1 := 'INSERT INTO example2(name';
  8    v_sql2 := 'SELECT name';
  9    FOR i IN 1..v_grades LOOP
 10  	 v_sql1 := v_sql1 || ',grade' || i;
 11  	 v_sql2 := v_sql2 || ',MAX(DECODE(dr,' || i || ',grade))';
 12    END LOOP;
 13    v_sql1 := v_sql1 || ') ';
 14    v_sql2 := v_sql1 || v_sql2
 15    || ' FROM (SELECT name,grade,DENSE_RANK()OVER (PARTITION BY name ORDER BY grade) dr FROM example1) GROUP BY name';
 16    DBMS_OUTPUT.PUT_LINE (v_sql2);
 17    EXECUTE IMMEDIATE v_sql2;
 18  END;
 19  /
INSERT INTO example2(name,grade1,grade2,grade3,grade4,grade5,grade6,grade7) SELECT
name,MAX(DECODE(dr,1,grade)),MAX(DECODE(dr,2,grade)),MAX(DECODE(dr,3,grade)),MAX(DECODE(dr,4,grade))
,MAX(DECODE(dr,5,grade)),MAX(DECODE(dr,6,grade)),MAX(DECODE(dr,7,grade)) FROM (SELECT
name,grade,DENSE_RANK()OVER (PARTITION BY name ORDER BY grade) dr FROM example1) GROUP BY name

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM example2
  2  /

NAME       GRADE1     GRADE2     GRADE3     GRADE4     GRADE5     GRADE6     GRADE7
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ABC        A          B          C          D          null       null       null
DEF        E          F          G          null       null       null       null

2 rows selected.