This discussion is archived
1 Reply Latest reply: Feb 10, 2010 5:07 PM by Tubby RSS

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

751969 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points