How to dynamically transpose data into with changing column headers with or without pivot/pivot xml?

Vinipanda

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.

Frank Kulash

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
;

Vinipanda

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.

Frank Kulash

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.