Solved

Generic Pivot Query

Posted on 2012-03-16
5
347 Views
Last Modified: 2012-03-19
This is in Oracle 10g

I have  a table say 'ABC' as follows..

ID      GCF      VAL1      VAL2
123       gcf1       581       48
123       gcf2       400       45
222       gcf1       100       55
222       gcf2       100       22
300       gcf1       20       40

I want a query to have a result something like below:

    ID      gcf1_val1   gcf1_val2   gcf2_val1   gcf2_val2
    123     581             48                400              45
    222     100             55                100              22
    300      20              40
.
The query needs to be expandible..Right now the table ABC has only 2 values for gcf which is gcf1 and gcf2..if a new value gcf lets say GCF3 comes in to ABC, the query should include gcf_3_val1, gcf3_val2 etc..

similarly if a new column say VAL3 is added to the table, then the query should give output all gcf values for the new column..ie
gcf1_val3, gcf2_val3, gcf3_val3 and so on..

I cannot use pivot operator as it is not available in 10g..

please let me know if there is a way..

Thanks
e
0
Comment
Question by:gs79
  • 3
  • 2
5 Comments
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
Comment Utility
In 10g one solution is to define  a type of record , a type of table based on that record and
write a pl/sql function using the pipe_row option in which you build the record

I choose for another approach : defining a view  and building dynamicaly the definition of that view by a procedure.

So, if there are gcf's added the query does not change dynamically but if you execute the procedure once you have a new pivot definition.


Procedure and test attached
semi-dynamic-pivot-test.txt
create-view-pivot-gfc.prc.txt
0
 

Author Comment

by:gs79
Comment Utility
Thanks flow01..

The view and procedure works great..

Right now it works fine if a new rows are added with different gcf values..

is there a way to expand this to dynamically accomodate  a new column  added to the source table..lets say if a new column val3 is added to the table then the query should give gcf1_val3, gcf2_val3 and so on..

Thanks
0
 
LVL 20

Expert Comment

by:flow01
Comment Utility
Yes ,
when you consider the parts in the resulting query where  val1 and val2 are involved , you see a repetition.
That repetition can be build dynamically within another loop .. end loop based on either a list of columnnames you can provide as an argument or by using the user_tab_columns view  
(for r_col in (select column_name
                    from user_tab_columns
                    where table_name = 'TABGCF'
                    and column_name not in ( 'ID','GCF')
                    order by COLUMN_ID) loop
..
END LOOP;
0
 

Author Comment

by:gs79
Comment Utility
g!et solution works like a charm! I included an inner loop to accomodate new metrics and the view spit out the new metrics along with new gcfs very well..I can even make this generic by parameterizing the procedure..

Just  wondering if we will the hit limitaton of varchar2. In the real time implementation of this program, we will be generating around 64 columns(8 X 8) dynamically and i am thinking v_statement variable will grow beyond 32 KB..

Do you know if there will be any work around in case if it happens..may be we need to define a clob?

Thanks
0
 
LVL 20

Expert Comment

by:flow01
Comment Utility
I saw some post that got around the 32kb limit by concatenating strings for the
execute immediate v_statement1 || v _statement2
Another limitation would be the maximum size of a create view-statement (i did not find it fast)

Same methods for creating the view you can use  however to create for example csv output; but that was not you asked for.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now