Dynamic pivot query?

ab_roman

Dear All,

I have a table data like below: table may have more record with different date.. if the table has more rows report colums need to increase to display the values.

param1 date1 value1 value2 value3

param2 date1 value1 value2 value3

param1 date2 value4 value5 value6

param2 date2 value4 value5 value6

...

I want to display like below in apex report:

Param	date			date			date...
param1	value1	value2	value3	value4	value5	value6
param2	value1	value2	value3	value4	value5	value6

I can't find a way to do this, could you please help...

any help is highly appreciated.

kind regards,

roman

AndrewSayer

Pivoting is a display requirement so should be handled by whatever is doing the displaying.

Since you’re using APEX, just create an interactive report using SQL that returns the data unpivoted, then use the report customizations to make it a pivot report. You can then save that report as the primary and APEX will do it all for you.

-edit

And here is a blog post I found via Google sharing some screenshots to help Monty Latiolais: Pivots Made Easy with APEX 5.0  saving it as a primary report is still required.

BluShadow

Agree with Andy, if you're using Apex, then use Apex functionality to achieve what you want.

Aside from that, one of the key things to understand is SQL Projection, and how Oracle needs to know the resultant column names/datatypes BEFORE any data is actually fetched from the query.  So you cannot have a dynamic number of columns based on the actual data that exists.

See the community document:   for an explanation.

John_K

It can be done - but with caution. Here is a direct copy/paste of a response I put on a similar thread a few days ago. But as already stated, it is better to do this in the application if possible.

Various people have tried, with varying success.http://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/But make sure you are aware of the potential risks etc.https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:4843682300346852395When Tom says "This is one time when I'd almost say we have ventured into "magic"" you probably want to take a deep breath before looking to use it in production code.  [https://asktom.oracle.com/pls/apex/asktom.search?tag=pivot-with-dynamic-number-of-columns-and-rows#5394721000346803830 ]