Discussions
Categories
- 384.5K All Categories
- 2.5K Data
- 551 Big Data Appliance
- 1.9K Data Science
- 451.3K Databases
- 222.1K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 32 Multilingual Engine
- 560 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 561 SQLcl
- 4K SQL Developer Data Modeler
- 187.4K SQL & PL/SQL
- 21.4K SQL Developer
- 296.8K Development
- Application Development
- 18 Developer Projects
- 140 Programming Languages
- 293.5K Development Tools
- 112 DevOps
- 3.1K QA/Testing
- 646.2K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 193 Java Community Process
- 106 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 21 Java Essentials
- 165 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
- 206 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 529 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 178 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 236 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 ]
