- 3,734,275 Users
- 2,246,935 Discussions
- 7,857,216 Comments
- 380.9K All Categories
- 2.1K Data
- 203 Big Data Appliance
- 1.9K Data Science
- 446.1K Databases
- 220.4K General Database Discussions
- 23 Multilingual Engine
- 506 MySQL Community Space
- 459 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 438 SQLcl
- 3.9K SQL Developer Data Modeler
- 185.4K SQL & PL/SQL
- 20.8K SQL Developer
- 291.3K Development
- 6 Developer Projects
- 117 Programming Languages
- 288.1K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.2K Java
- 18 Java Learning Subscription
- 36.9K Database Connectivity
- 149 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.7K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 12 Java Essentials
- 138 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 195 Java User Groups
- 180 LiveLabs
- 34 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 165 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
How to dynamically transpose data into with changing column headers with or without pivot/pivot xml?
I have 2 scenarios for dynamic pivoting.
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
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.
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:
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.
The solution could be with/without pivot or we can also use collections/arrays if possible. ANything that helps in changing the headers dynamically.