I have two tables (TABLE1 and TABLE2).

I need to transpose the data from TABLE1 to TABLE2.

Exmaple as below:

TABLE1 data looks like:


     STDNO SUBJ       MARKS
---------- ----- ----------
         1 A             50 
         1 B            100 
         1 C             25 
         1 D             50 
         1 E            100 
         1 F             20 
         1 G             40 
         1 H             60 
         1 I             80 
         1 J            100 
         1 K            100 

 11 rows selected 

And the TABLE2 data have to be like this:

    STDNO SUBJ1     MARKS1 SUBJ2     MARKS2 SUBJ3     MARKS3 SUBJ4     MARKS4 SUBJ5     MARKS5
---------- ----- ---------- ----- ---------- ----- ---------- ----- ---------- ----- ----------
         1 A             50 B            100                                                    
         1 C             25 D             50 E            100                                   
         1 F             20 G             40 H             60 I             80 J            100 
         1 K            100                                                                     


Transposing data from TABLE1 to TABLE2 and to split into new rows whenever the MARKS field has the value as '100'