Home » SQL & PL/SQL » SQL & PL/SQL » Transposing a n/n table
Transposing a n/n table [message #35660] Thu, 04 October 2001 07:00 Go to next message
sriram
Messages: 58
Registered: September 2000
Member
Dear Friends,

I have a n/n table. I mean, a table which has n rows and n columns ok, now i intend to transpose the same table. How do i do it...? any help is highly appreciated.

Thanks in advance
Regards
Sriram

----------------------------------------------------------------------
Re: Transposing a n/n table [message #35664 is a reply to message #35660] Thu, 04 October 2001 11:35 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
see if this helps.
http://128.121.241.221/Pipelines/PLSQL/archives.htm#code16

----------------------------------------------------------------------
Re: Transposing a n/n table [message #35679 is a reply to message #35660] Fri, 05 October 2001 05:03 Go to previous messageGo to next message
Hans
Messages: 42
Registered: September 2000
Member
I tried to solve this problem without the use of dynamic sql. Here are my results

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;
 



----------------------------------------------------------------------
Re: Transposing a n/n table [message #35682 is a reply to message #35660] Fri, 05 October 2001 06:28 Go to previous messageGo to next message
sriram
Messages: 58
Registered: September 2000
Member
Hi Hans!
Thanx for showing interest in solving the problem.
Your way of solving it was good and especially the way you have presented it.
But, you have hardcoded the number of columns and rows....and this may not be the case. That is the reason i have specified n/n instead of 3/3 or 4/4 ....etc.
I have got a solution for this.....this morning itself.....here it goes......:
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;
/
Re: Transposing a n/n table [message #35684 is a reply to message #35660] Fri, 05 October 2001 06:34 Go to previous message
sriram
Messages: 58
Registered: September 2000
Member
Thanx a lot Andrew! thats a wonderful place to look out for solutions..!

Thanx a lott

Regards
Sriram

----------------------------------------------------------------------
Previous Topic: Maximum Open Cursors Exceeded
Next Topic: Re: Max Open Cursors
Goto Forum:
  


Current Time: Sat May 25 15:18:38 CDT 2024