Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Dynamic pivot query?

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
Answers
-
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.
-
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.
-
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 ]