Skip to Main Content
  • Questions
  • How to dynamically transpose data into with changing column headers with or without pivot/pivot xml?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Vini.

Asked: November 24, 2020 - 4:05 am UTC

Last updated: December 02, 2020 - 9:55 am UTC

Version: Oracle 12c

Viewed 10K+ times! This question is

You Asked

This question is most common with no clear solution.
I have 2 scenarios for dynamic pivoting and the solution requested could be using pivot xml(tried, but extracting is a task)/dynamic sql execution/arays if possible we could use..

Scenario 1:

In the first case,I i need to pivot the data where data source is same table. However the pivot needs to be dynamic as the columns header would keep changing as per column app_id. So if app_id=1. The column header would be A,B,C,D, If app_id=2, column would be CDEF and so on. Also each set of value has an id. So for id, 120 and app_id=1 , column A,B,C,D, would display the values and so on.

The current sample data has only 2 app_ids, but there could be many more, so app_id and labels would kepe changing thus i need to write a dynamic query.

Table is DATA_1

ID  label   value   app_id
--- -----   -----   ------
120 A       Alpha   1
120 B       Beta    1
120 C       Class   1
120 D       Delta   1
120 C       Alpha   2
120 D       Beta    2
120 E       Class   2
120 F       Delta   2

And expected output would be something like this.

P.S. Data is dummy and app_ids, and ids would keep getting inserted and removed with changing column headers,so a dynamic solution is needed.

SELECT * FROM data WHERE ID = 120 AND app_id = 1;    
app_id  A        B      C      D      ID
------  ------   -----  -----  -----  -----
1       Alpha    Beta   Class  Delta  120

SELECT * FROM data WHERE ID = 120 AND app_id = 2;    
app_id  C        D      E      F      ID
------  ------   -----  -----  -----  -----
2       Alpha    Beta   Class  Delta  120



Scenario 2:

Here were had the data in one table. Now we have separate table containing the labels and current table with values.

SO in table from scenario one we will focus on id, app_id and value columns only.

Select app_id,id,value from data_1;

The labels will come from another table DATA_Labels in column Header_Name:

APP_ID SEQ HEADER_NAME
1 1 A
1 2 B
1 3 C
1 4 D
1 5 E
1 6 F
1 7 G
1 8 H
1 9 I
1 10 J
2 1 P
2 2 Q
2 3 R
2 4 S
2 5 T
2 6 U
2 7 V
2 8 W
2 9 X
2 10 Y


So, for labels we would use header_names. ANd Data_1 and Data_Labels could be joined on basis of app_id.

In case the values exceed column headers, we can assume that the headers would reach say maximum 20. And in case the header/name is not available, it could put some default header name like COL11,COL12 if the value stretches upto there.

I did lot of research but most solutions are too complex and confusing. Any leads would be appreciated.

and Chris said...

There's no good solution to this problem sadly.

Options include:

- Running a query first to generate the pivot clause

select listagg ( '''' || header_name || ''' as ' || header_name, ',' ) 
         within group ( order by seq ) 
from   data_labels
where  app_id = :app


Then concatenating the output of this to a dynamic SQL statement querying your table

- Using custom types

Get Anton Scheffer's pivot function, which allows you to pass a query in to generate the columns:

https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

Both of these have drawbacks; I discuss these further in the Dynamic Column Lists section of my guide to pivot/unpivot:

https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Generic pivot

Rajeshwaran, Jeyabal, December 01, 2020 - 10:01 am UTC

How about a generic pivot like this?

drop table t purge;
create table t( id number, label varchar2(5), label_value varchar2(10), app_id number );
insert into t values(120,'A','Alpha',1);
insert into t values(120,'B','Beta',1);
insert into t values(120,'C','Class',1);
insert into t values(120,'D','Delta',1);
insert into t values(120,'C','Alpha',2);
insert into t values(120,'D','Beta',2);
insert into t values(120,'E','Class',2);
insert into t values(120,'F','Delta',2);
commit;

demo@XEPDB1> select label,label_value,id from t where app_id = 1;

LABEL LABEL_VALU         ID
----- ---------- ----------
A     Alpha             120
B     Beta              120
C     Class             120
D     Delta             120

demo@XEPDB1>
demo@XEPDB1> create or replace function generic_pivot(
  2     p_source in varchar2,
  3     p_pivotfor in varchar2,
  4     p_aggfns in varchar2
  5  )
  6  return sys_refcursor as
  7     l_sql dbms_sql.Varchar2_Table ;
  8     l_result sys_refcursor;
  9     l_pivot_in_list long;
 10  begin
 11     l_sql(1) := q'$ select listagg(''''||#P_PIVOTFOR# ||''' as "'||#P_PIVOTFOR#||'"',',') within group ( order by #P_PIVOTFOR# )
 12                     from ( select distinct #P_PIVOTFOR# from #P_SOURCE# ) $' ;
 13     l_sql(2) := q'$ select * from #P_SOURCE#
 14                                     pivot( #P_AGGFNS#
 15                                             for #P_PIVOTFOR# in (#PIVOT_IN_LIST#) ) order by 1,2 $';
 16
 17     l_sql(1) := replace( l_sql(1) ,'#P_PIVOTFOR#',p_pivotfor);
 18     l_sql(1) := replace( l_sql(1) ,'#P_SOURCE#',p_source);
 19
 20     execute immediate l_sql(1) into l_pivot_in_list;
 21
 22     l_sql(2) := replace( l_sql(2),'#P_SOURCE#',p_source);
 23     l_sql(2) := replace( l_sql(2),'#P_AGGFNS#',p_aggfns);
 24     l_sql(2) := replace( l_sql(2),'#P_PIVOTFOR#',p_pivotfor);
 25     l_sql(2) := replace( l_sql(2),'#PIVOT_IN_LIST#',l_pivot_in_list);
 26
 27
 28     open l_result for l_sql(2);
 29     return l_result;
 30  end;
 31  /

Function created.

demo@XEPDB1> variable r refcursor
demo@XEPDB1> begin
  2     :r := generic_pivot(
  3             p_source=>' (select label,label_value,id from t where app_id = 1) ',
  4             p_pivotfor => 'label',
  5             p_aggfns =>' max(label_value) ' );
  6  end;
  7  /

PL/SQL procedure successfully completed.

demo@XEPDB1> print r

        ID A          B          C          D
---------- ---------- ---------- ---------- ----------
       120 Alpha      Beta       Class      Delta

demo@XEPDB1> begin
  2     :r := generic_pivot(
  3             p_source=>' (select label,label_value,id from t where app_id = 2) ',
  4             p_pivotfor => 'label',
  5             p_aggfns =>' max(label_value) ' );
  6  end;
  7  /

PL/SQL procedure successfully completed.

demo@XEPDB1> print r

        ID C          D          E          F
---------- ---------- ---------- ---------- ----------
       120 Alpha      Beta       Class      Delta

demo@XEPDB1>

Chris Saxon
December 01, 2020 - 5:46 pm UTC

It does show the principle of using listagg to generate the columns.

You'll have to think carefully about the SQL injection risks with a function like this though. I'd prefer to limit the queries to specific tables as much as possible to keep your data safe.

Generic pivot

Rajeshwaran, Jeyabal, December 02, 2020 - 4:37 am UTC

thanks - as mentioned it is an approach. to be free from sql-injection attached, one should have it customized using dbms_assert where ever it is needed.
Chris Saxon
December 02, 2020 - 9:55 am UTC

The big issue is you're accepting a query to generate the column names. This could be anything!

e.g.:

begin
   :r := generic_pivot(
           p_source=>' (select label,label_value,id from t where app_id = 1
           union all select table_name, table_name, -99 from user_tables) ',
           p_pivotfor => 'label',
           p_aggfns =>' max(label_value) ' );
end;
/


It's very hard to defend against this because the intent is to accept any query and run it.

Generic pivot

Rajeshwaran, Jeyabal, December 03, 2020 - 4:28 am UTC

....
It's very hard to defend against this because the intent is to accept any query and run it.
....


That's right - since the sql is not constructed at the database, so it is hard to detect the sql-injection here - rather sql-injection check should be done on those layer where the sql is getting constructed.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.