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
How to dynamically transpose data into with changing column headers with or without pivot/pivot xml?

I have 2 scenarios for dynamic pivoting.
Scenario 1:
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.
Scenario 2:
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.
Answers
-
HI,
If you really need a dynamic number of columns, then you need dynamic SQL.
If you would be satisfied with a huge string column that was formatted to look like a dynamic number of columns, then see:
https://community.oracle.com/tech/developers/discussion/4056010/pivot-sql
This site re-formats old posts, so the code in that thread may be hard to read. Here's the query from the last reply:
WITH all_dept_ids AS ( SELECT dept_id , ROW_NUMBER () OVER (ORDER BY dept_id) AS col_number FROM a_table GROUP BY dept_id ) , union_results AS ( SELECT 3 AS part_number , t.id_number , TO_CHAR (t.id_number, '99999999') AS id_number_str , LISTAGG ( LPAD ( NVL (t.dept_name, ' ') , 10 ) , ' ' ) WITHIN GROUP (ORDER BY d.col_number) AS txt FROM all_dept_ids d LEFT OUTER JOIN a_table t PARTITION BY (t.id_number) ON t.dept_id = d.dept_id GROUP BY t.id_number UNION ALL SELECT 1 , NULL , 'ID_NUMBER' , LISTAGG ( LPAD ( TO_CHAR (dept_id) , 10 ) , ' ' ) WITHIN GROUP (ORDER BY col_number) FROM all_dept_ids UNION ALL SELECT 2 , NULL , '---------' , LISTAGG ( LPAD ('-', 10, '-') , ' ' ) WITHIN GROUP (ORDER BY col_number) FROM all_dept_ids ) SELECT id_number_str , txt FROM union_results ORDER BY part_number ;
-
I am unable to understand what this query is doing to be honest and how i can implement this in my scenario.
But thank you for the reference.
-
HI,
I am unable to understand what this query is doing ...
You need to read the other thread. Click on the link in the first reply.