[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Generic Pivot Query

Posted on 2012-03-16
5
Medium Priority
?
376 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 21

Accepted Solution

by:
flow01 earned 2000 total points
ID: 37732452
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
ID: 37732705
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 21

Expert Comment

by:flow01
ID: 37734653
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
ID: 37736307
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 21

Expert Comment

by:flow01
ID: 37738599
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month18 days, 16 hours left to enroll

834 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