1 Reply Latest reply: Feb 10, 2010 7:07 PM by Tubby RSS

    PIVOTING / TRANSPOSE ROW TO COLUMN / RUN A SQL STATEMENT STORED IN A TABLE

    751969
      In my last post (RUN A SQL STATEMENT STORED IN A TABLE) I got support from many and developed the following code. Hope this will be helpful, not affecting the performance of database.. atlest not more then the pivot / pivot xml function of 11g.

      The procedure can be downloaded from http://www.box.net/shared/qpe9bbj4nn

      Step 1 Create Table ROTOCOL0
      ------------------------------------------------------------------------
      CREATE TABLE "ROTCOL0"
      (     "MAT" NUMBER,
           "MATGRP" NUMBER,
           "UOM" VARCHAR2(10),
           "VALUE" NUMBER
      )
      /
      Step: 2 Insert Data:
      ------------------------------------------------------------------------
      Insert into rotocol0 values (1001, 101, 'PC', 123);
      Insert into rotocol0 values (1002, 101, 'PC', 456);
      Insert into rotocol0 values (1003, 102, 'PC', 789);
      Insert into rotocol0 values (1004, 102, 'PC', 147);
      Insert into rotocol0 values (1005, 103, 'PC', 258);
      Insert into rotocol0 values (1006, 103, 'PC', 369);
      Insert into rotocol0 values (1001, 101, 'PC', 123);
      Insert into rotocol0 values (1002, 101, 'PC', 456);
      Insert into rotocol0 values (1003, 102, 'PC', 789);
      Insert into rotocol0 values (1004, 102, 'PC', 147);
      Insert into rotocol0 values (1005, 103, 'PC', 258);
      Insert into rotocol0 values (1006, 103, 'PC', 369);
      Insert into rotocol0 values (1001, 101, 'PC', 123);
      Insert into rotocol0 values (1002, 101, 'PC', 456);
      Insert into rotocol0 values (1003, 102, 'PC', 789);
      Insert into rotocol0 values (1004, 102, 'PC', 147);
      Insert into rotocol0 values (1005, 103, 'PC', 258);
      Insert into rotocol0 values (1006, 103, 'PC', 369);
      Insert into rotocol0 values (1001, 101, 'PC', 123);
      Insert into rotocol0 values (1002, 101, 'PC', 456);
      Insert into rotocol0 values (1003, 102, 'PC', 789);
      Insert into rotocol0 values (1004, 102, 'PC', 147);
      Insert into rotocol0 values (1005, 103, 'PC', 258);
      Insert into rotocol0 values (1006, 103, 'PC', 369);
      Insert into rotocol0 values (1001, 101, 'PC', 123);
      Insert into rotocol0 values (1002, 101, 'PC', 456);
      Insert into rotocol0 values (1003, 102, 'PC', 789);
      Insert into rotocol0 values (1004, 102, 'PC', 147);
      Insert into rotocol0 values (1005, 103, 'PC', 258);
      Insert into rotocol0 values (1006, 103, 'PC', 369);
      Insert into rotocol0 values (1001, 101, 'PC', 123);
      Insert into rotocol0 values (1002, 101, 'PC', 456);
      Insert into rotocol0 values (1003, 102, 'PC', 789);
      Insert into rotocol0 values (1004, 102, 'PC', 147);
      Insert into rotocol0 values (1005, 103, 'PC', 258);
      Insert into rotocol0 values (1006, 103, 'PC', 369);

      Step 3 Check inserted data
      ------------------------------------------------------------------------
      Select * from rotocol0;
      =
      MAT MATGRP UOM VALUE
      1003 102 PC 789
      1004 102 PC 147
      1005 103 PC 258
      1006 103 PC 369
      1003 102 PC 789
      1001 101 PC 123
      1002 101 PC 456
      1004 102 PC 147
      1005 103 PC 258
      1006 103 PC 369
      1001 101 PC 123
      1002 101 PC 456
      1003 102 PC 789
      1004 102 PC 147
      1005 103 PC 258
      1006 103 PC 369
      1003 102 PC 789
      1001 101 PC 123
      1002 101 PC 456
      1004 102 PC 147
      1005 103 PC 258
      1006 103 PC 369
      1001 101 PC 123
      1002 101 PC 456

      24 rows returned in 0.01 seconds CSV Export
      DBMS Output

      Setp 4 Creat Function GITESH
      ------------------------------------------------------------------------
      create or replace FUNCTION GITESH( p_slct IN VARCHAR2,

      p_dlmtr IN VARCHAR2 DEFAULT '     ' ) RETURN VARCHAR2

      AUTHID CURRENT_USER AS





      TYPE c_refcur IS REF CURSOR;

      lc_str VARCHAR2(4000);

      lc_colval VARCHAR2(4000);

      c_dummy c_refcur;

      l number;


      BEGIN


      OPEN c_dummy FOR p_slct;



      LOOP

      FETCH c_dummy INTO lc_colval;

      EXIT WHEN c_dummy%NOTFOUND;

      lc_str := lc_str || p_dlmtr || lc_colval;

      END LOOP;

      CLOSE c_dummy;



      RETURN SUBSTR(lc_str,2);


      EXCEPTION

      WHEN OTHERS THEN



      lc_str := SQLERRM;

      IF c_dummy%ISOPEN THEN

      CLOSE c_dummy;

      END IF;

      RETURN lc_str;




      END;




      STEP 5 RUN FOLLOWING STATEMENT to create view ROTOCOL1
      ------------------------------------------------------------------------

      DECLARE
      A varchar2(4000);
      BEGIN
      Select 'CREATE OR REPLACE FORCE VIEW "ROTOCOL1" ("A") AS SELECT '||substr('''',0)||' CREATE OR REPLACE FORCE VIEW

      "ROTOCOL3" AS Select A.MAT'||substr('''',0)||' AS A from dual union SELECT DISTINCT     '||substr('''',0)||', SUM(CASE

      WHEN A.MATGRP = '||substr('''',0)||'||B.MATGRP||'||substr('''',0)||' THEN A.VALUE ELSE 0 END) AS A_'||substr

      ('''',0)||'||B.MATGRP AS A FROM ROTOCOL0 B UNION SELECT '||substr('''',0)||'FROM ROTOCOL0 A GROUP BY A.MAT'||substr

      ('''',0)||' as A From dual Order by 1' into A from dual;

      EXECUTE IMMEDIATE A;

      END;


      Step 6 - RUN FOLLOWING STATEMENT to create view ROTOCOL2
      ------------------------------------------------------------------------
      DECLARE
      B varchar2(4000);
      BEGIN
      Select 'CREATE OR REPLACE FORCE VIEW "ROTOCOL2" ("Z") AS SELECT DISTINCT GITESH('||substr('''',0)||'SELECT DISTINCT

      a FROM ROTOCOL1'||substr('''',0)||') as Z FROM ROTOCOL1 A' into B FROM DUAL;
      EXECUTE IMMEDIATE B;
      END;

      ------------------------------------------------------------------------
      Step 7 - RUN FOLLOWING STATEMENT to create view ROTOCOL3
      ------------------------------------------------------------------------

      DECLARE
      c VARCHAR2(4000);
      BEGIN
      SELECT Z INTO c FROM ROTOCOL2;
      EXECUTE IMMEDIATE c;
      END;

      ------------------------------------------------------------------------
      Step 8 - Drop view ROTOCOL1 |tHIS sTEP iS oPTIONAL, cAN bE iGNORED|
      ------------------------------------------------------------------------

      DECLARE
      c VARCHAR2(100);
      BEGIN
      SELECT 'drop view ROTOCOL1' FROM ROTOCOL2;
      EXECUTE IMMEDIATE c;
      END;

      ------------------------------------------------------------------------
      Step 9 - Drop view ROTOCOL2 |tHIS iS sTEP oPTIONAL, cAN bE iGNORED|
      ------------------------------------------------------------------------

      DECLARE
      c VARCHAR2(100);
      BEGIN
      SELECT 'drop view ROTOCOL2' FROM ROTOCOL2;
      EXECUTE IMMEDIATE c;
      END;
      ------------------------------------------------------------------------
      Step 8 - Check Result
      ------------------------------------------------------------------------

      Select * from ROTOCOL3

      The result should be

      MAT A_101 A_102 A_103
      1003 0 3156 0
      1006 0 0 1476
      1001 492 0 0
      1002 1824 0 0
      1004 0 588 0
      1005 0 0 1032

      Put All the querys in procedure / package or wherever they suit and "Aesh Karo";)

      Ping me back in case u have problem with this code.

      "Simple logic, High results"

      Regards,

      Gitesh Shah
        • 1. Re: PIVOTING / TRANSPOSE ROW TO COLUMN / RUN A SQL STATEMENT STORED IN A TABLE
          Tubby
          Using the
           tags to format your code may help you get a response sooner than later.
          
          This and other useful pieces of information are located in the  FAQ
          
          http://wiki.oracle.com/page/Oracle+Discussion+Forums+FAQ