1 person found this helpful
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.
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.
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: PL/SQL 101 : Cursors and SQL Projection for an explanation.
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.
But make sure you are aware of the potential risks etc.
When 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 ]