asked on
Generic Pivot Query
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
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
Oracle Database
Last Comment
flow01
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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;
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;
ASKER
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
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
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.
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.
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