drop table r;
create table r (
   r1    number(3),
   r2    number(3),
   r3    number(3),
   r4    number(3)
);
 
insert into r values (  1,  2,  3, 4 );
insert into r values (  5,  6,  7, 8 );
insert into r values (  9, 10, 11, 12 );
insert into r values ( 13, 14, 15, 16 );
 
 
drop table s;
create table s (
   s1    number(3),
   s2    number(3),
   s3    number(3),
   s4    number(3)
);
 
 
-- to avoid a complex SQL-Statement i create the view v
create or replace view v as
   select a.rid, b.ridnum, a.colnum, a.colval from
      (
         select rid, colval, colnum from
            (
               select rowid rid, r1 colval, 1 colnum from r
               union all
               select rowid rid, r2 colval, 2 colnum from r
               union all
               select rowid rid, r3 colval, 3 colnum from r
               union all
               select rowid rid, r4 colval, 4 colnum from r
            )
         group by rid, colnum, colval
      ) a,
      (
         select rid, rownum ridnum from
         (
            select rid from
            (
               select rowid rid from r
            )
            group by rid
         )
      ) b
   where a.rid = b.rid;
 
 
-- final insert-Statement
insert into s ( s1, s2, s3, s4 )
   select a.colval s1, b.colval s2, c.colval s3, d.colval s4 from
      (
         select colnum, colval from v
            where ridnum = 1
      ) a,
      (
         select colnum, colval from v
            where ridnum = 2
      ) b,
      (
         select colnum, colval from v
            where ridnum = 3
      ) c,
      (
         select colnum, colval from v
            where ridnum = 4
      ) d
   where a.colnum = b.colnum
   and   b.colnum = c.colnum
   and   c.colnum = d.colnum;
   
 
----------------
-- Test
----------------
select * from r
   order by r1;
 
        R1         R2         R3         R4
---------- ---------- ---------- ----------
         1          2          3          4
         5          6          7          8
         9         10         11         12
        13         14         15         16
        
4 rows selected
 
 
select * from s
   order by  s1;
 
        S1         S2         S3         S4
---------- ---------- ---------- ----------
         1          5          9         13
         2          6         10         14
         3          7         11         15
         4          8         12         16
 
4 rows selected.  
 
 
 
 
 
-- Insert-Statement witout using the view v
delete from s;
 
insert into s ( s1, s2, s3, s4 )
   select a.colval s1, b.colval s2, c.colval s3, d.colval s4 from
      (
         select colnum, colval from
            (
               select a.rid, b.ridnum, a.colnum, a.colval from
                  (
                     select rid, colval, colnum from
                        (
                           select rowid rid, r1 colval, 1 colnum from r
                           union all
                           select rowid rid, r2 colval, 2 colnum from r
                           union all
                           select rowid rid, r3 colval, 3 colnum from r
                           union all
                           select rowid rid, r4 colval, 4 colnum from r
                        )
                     group by rid, colnum, colval
                  ) a,
                  (
                     select rid, rownum ridnum from
                     (
                        select rid from
                        (
                           select rowid rid from r
                        )
                        group by rid
                     )
                  ) b
               where a.rid = b.rid           
            )
            where ridnum = 1
      ) a,
      (
         select colnum, colval from
            (
               select a.rid, b.ridnum, a.colnum, a.colval from
                  (
                     select rid, colval, colnum from
                        (
                           select rowid rid, r1 colval, 1 colnum from r
                           union all
                           select rowid rid, r2 colval, 2 colnum from r
                           union all
                           select rowid rid, r3 colval, 3 colnum from r
                           union all
                           select rowid rid, r4 colval, 4 colnum from r
                        )
                     group by rid, colnum, colval
                  ) a,
                  (
                     select rid, rownum ridnum from
                     (
                        select rid from
                        (
                           select rowid rid from r
                        )
                        group by rid
                     )
                  ) b
               where a.rid = b.rid           
            )
            where ridnum = 2
      ) b,
      (
         select colnum, colval from
            (
               select a.rid, b.ridnum, a.colnum, a.colval from
                  (
                     select rid, colval, colnum from
                        (
                           select rowid rid, r1 colval, 1 colnum from r
                           union all
                           select rowid rid, r2 colval, 2 colnum from r
                           union all
                           select rowid rid, r3 colval, 3 colnum from r
                           union all
                           select rowid rid, r4 colval, 4 colnum from r
                        )
                     group by rid, colnum, colval
                  ) a,
                  (
                     select rid, rownum ridnum from
                     (
                        select rid from
                        (
                           select rowid rid from r
                        )
                        group by rid
                     )
                  ) b
               where a.rid = b.rid           
            )
            where ridnum = 3
      ) c,
      (
         select colnum, colval from
            (
               select a.rid, b.ridnum, a.colnum, a.colval from
                  (
                     select rid, colval, colnum from
                        (
                           select rowid rid, r1 colval, 1 colnum from r
                           union all
                           select rowid rid, r2 colval, 2 colnum from r
                           union all
                           select rowid rid, r3 colval, 3 colnum from r
                           union all
                           select rowid rid, r4 colval, 4 colnum from r
                        )
                     group by rid, colnum, colval
                  ) a,
                  (
                     select rid, rownum ridnum from
                     (
                        select rid from
                        (
                           select rowid rid from r
                        )
                        group by rid
                     )
                  ) b
               where a.rid = b.rid           
            )
            where ridnum = 4
      ) d
   where a.colnum = b.colnum
   and   b.colnum = c.colnum
   and   c.colnum = d.colnum;
 

CREATE OR REPLACE FUNCTION tabcount (
   sch IN VARCHAR2,
   tab IN VARCHAR2)
   RETURN INTEGER
IS
/*
|| Generic function utilizing dynamic SQL to return the 
|| number of rows in the specified table.
||
|| Author: Steven Feuerstein
|| Source: PL/SQL Pipeline (www.revealnet.com/plsql-pipeline)
||
|| Dependencies:
||    DBMS_SQL -- Oracle dynamic SQL package
*/
   cur     INTEGER  := DBMS_SQL.OPEN_CURSOR;
   ignore  INTEGER;
   retval  INTEGER;
BEGIN
   DBMS_SQL.PARSE (cur, 'SELECT COUNT(*) FROM ' || sch || '.' || tab, DBMS_SQL.NATIVE);
   DBMS_SQL.DEFINE_COLUMN (cur, 1, retval);
   ignore := DBMS_SQL.EXECUTE_AND_FETCH (cur);
   DBMS_SQL.COLUMN_VALUE (cur, 1, retval);
   DBMS_SQL.CLOSE_CURSOR (cur);
   RETURN retval;
END;
/

CREATE OR REPLACE PROCEDURE transpose (
   source_owner IN VARCHAR2,
   source_table IN VARCHAR2,
   target_owner IN VARCHAR2,
   target_table IN VARCHAR2,
   showaction IN BOOLEAN := FALSE
   )
IS
/*
|| Procedure to transpose a table (columns to rows, 
|| rows to columns). In Oracle7, this means that the 
|| table may not have more than 254 rows. In Oracle8,
|| the limit is raised to 1000 rows.
||
|| You must create the transpose table (target_table)
|| before you run this procedure. That way, you get to
|| determine the transposed column names.
||
|| Author: Solomon Yakobson
|| Source: PL/SQL Pipeline (www.revealnet.com/plsql-pipeline)
||
|| Dependencies:
||    tabcount -- see above
||    DBMS_SQL -- Oracle dynamic SQL package
*/
   rcount  NUMBER := tabcount (source_owner, source_table);
   cur     INTEGER;
   ignore  INTEGER;
   stmt    VARCHAR2(32767);
   prefix  CHAR(1);
    
   cur_id number(6);
   int_ret number(6)	;
   str_query varchar2(32767);	
	

   CURSOR colcur (
      s_owner ALL_TAB_COLUMNS.owner%TYPE,
      s_table ALL_TAB_COLUMNS.table_name%TYPE
      )
   IS
      SELECT column_name
        FROM ALL_TAB_COLUMNS
       WHERE owner = UPPER (s_owner)
         AND table_name = UPPER (s_table)
       ORDER BY column_id;
BEGIN
   IF rcount > 0
   THEN
      cur := DBMS_SQL.OPEN_CURSOR;
      FOR colrec IN colcur (source_owner, source_table)
      LOOP
         prefix := NULL;
         stmt :=
            'INSERT INTO ' || target_owner || '.' || target_table || ' SELECT ';

         FOR rnum IN 1 .. rcount
         LOOP
            stmt := stmt || prefix || 'MAX(DECODE(ROWNUM,' || rnum || ',' || colrec.column_name || ',NULL))';
            IF rnum = 1
            THEN
               prefix := ',';
            END IF;
         END LOOP;

         stmt := stmt || ' FROM ' || source_owner || '.' || source_table;

         IF showaction
         THEN
           -- p.l (stmt);
		dbms_output.put_line(stmt);
         END IF;

         DBMS_SQL.PARSE (cur, stmt, DBMS_SQL.native);

         ignore := DBMS_SQL.EXECUTE (cur);

         IF showaction
         THEN
--            p.l ('INSERT result', ignore);
		dbms_output.put_line('INSERT result'|| ignore);
         END IF;
      END LOOP;

      DBMS_SQL.CLOSE_CURSOR (cur);

--Added Code By  Sriram .....
--The Code By Steven , transposes the table into another such table.
--Code Now Added would transpose the table into itself.
	cur_id := dbms_sql.open_cursor;
	STR_QUERY := 'DELETE FROM ' || SOURCE_TABLE ; 
	dbms_sql.parse(cur_id,STR_QUERY, DBMS_SQL.NATIVE);
	INT_RET := DBMS_SQL.EXECUTE(CUR_ID);
	STR_QUERY := 'INSERT INTO ' || SOURCE_TABLE || ' SELECT * FROM ' || TARGET_TABLE ;
	dbms_sql.parse(cur_id,STR_QUERY, DBMS_SQL.NATIVE);
	INT_RET := DBMS_SQL.EXECUTE(CUR_ID);
	DBMS_SQL.CLOSE_CURSOR(CUR_ID);
	COMMIT;		
   END IF;
END;
/