Use pivot for dynamically changing column headers using sql in oracle

Question

I have a requirement where i need to pivot the data. 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.

Sample table Data:

ID  label   value   app_id
--- -----   -----   ------
120 A       Alpha   1
120 B       Beta    1
120 C       Class   1
120 D       Delta   1
120 C       Alpha   2
120 D       Beta    2
120 E       Class   2
120 F       Delta   2

and the constructive query is

WITH data( ID, label, value, app_id ) AS
(
  SELECT 120, 'A', 'Alpha', 1 FROM dual UNION ALL
  SELECT 120, 'B', 'Beta' , 1 FROM dual UNION ALL
  SELECT 120, 'C', 'Class', 1 FROM dual UNION ALL
  SELECT 120, 'D', 'Delta', 1 FROM dual UNION ALL
  SELECT 120, 'C', 'Alpha', 2 FROM dual UNION ALL
  SELECT 120, 'D', 'Beta' , 2 FROM dual UNION ALL
  SELECT 120, 'E', 'Class', 2 FROM dual UNION ALL
  SELECT 120, 'F', 'Delta', 2 FROM dual  
)
SELECT * 
  FROM data

Expected output:

SELECT * FROM data WHERE ID = 120 AND app_id = 1;    
app_id  A        B      C      D      ID
------  ------   -----  -----  -----  -----
1       Alpha    Beta   Class  Delta  120

SELECT * FROM data WHERE ID = 120 AND app_id = 2;    
app_id  C        D      E      F      ID
------  ------   -----  -----  -----  -----
2       Alpha    Beta   Class  Delta  120

Answer 1

What you can do

SELECT * 
  FROM data  
 PIVOT  
 (
  MAX(value) FOR label IN ('A' AS "A", 'B' AS "B",'C' AS "C",'D' AS "D")
 )
 WHERE ID = 120 AND app_id = 1

as a static pivot statement might be converted to a function which contains two respective parameters

CREATE OR REPLACE FUNCTION Get_Pivoted_Labels( i_id data.id%type, i_app_id data.app_id%type ) 
RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767); 
  v_cols      VARCHAR2(32767);  
BEGIN
  SELECT LISTAGG( ''''||label||''' AS "'||label||'"' , ',' )
          WITHIN GROUP ( ORDER BY label ) 
    INTO v_cols
    FROM ( SELECT DISTINCT label 
             FROM data
            WHERE ID = i_id AND app_id = i_app_id );

  v_sql :=
      'SELECT * 
         FROM data
        PIVOT 
        (
          MAX(value) FOR label IN ( '|| v_cols ||' )
        )
       WHERE ID = :id AND app_id = :aid'; 

  OPEN v_recordset FOR v_sql USING i_id, i_app_id;
  RETURN v_recordset;
END;
/

in which an auxiliary query, in which the label columns are distinctly selected, is used to determine the string(v_cols for 'A' AS "A", 'B' AS "B",'C' AS "C",'D' AS "D") to be concatenated to the main SQL string in order to be used within the cursor which returns a value of type SYS_REFCURSOR.

and is invoked by

VAR rc REFCURSOR
VAR v_id NUMBER
VAR v_app_id NUMBER
EXEC :rc := Get_Pivoted_Labels(:v_id,:v_app_id);
PRINT rc

from SQL developer's console.

Demonstration with generated SQL statements

If order of columns in the SELECT list matters, then use the code below in order to create the function

CREATE OR REPLACE FUNCTION Get_Pivoted_Labels( i_id data.id%type, i_app_id data.app_id%type ) 
RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767); 
  v_cols_1    VARCHAR2(32767);    
  v_cols_2    VARCHAR2(32767);   
BEGIN
  SELECT LISTAGG( ''''||label||''' AS "'||label||'"' , ',' )
          WITHIN GROUP ( ORDER BY label ),
         LISTAGG( label , ',' )
          WITHIN GROUP ( ORDER BY label )  
    INTO v_cols_1, v_cols_2
    FROM ( SELECT DISTINCT label, value 
             FROM data
            WHERE ID = i_id AND app_id = i_app_id );

  v_sql :=
      'SELECT ID, '|| v_cols_2 ||', app_id
         FROM data
        PIVOT 
        (
          MAX(value) FOR label IN ( '|| v_cols_1 ||' )
        )
       WHERE ID = :id AND app_id = :aid'; 

  OPEN v_recordset FOR v_sql USING i_id, i_app_id;
  RETURN v_recordset;
END;
/

Answer 2

This is a pretty common question, for the simple reason that the answer is "no".

Using the Oracle database, every SELECT statement must have a fixed and known "shape" (the number of columns, their names and their data types). In recent versions, there are "polymorphic table functions" that seem to break this rule, but in fact they do not: the "shape" is calculated when the statement is parsed, so it is fixed and known before execution starts.

You don't want "polymorphic" (shape changing at parse time), you want truly "dynamic" (shape changing at execution time based on the data). Oracle doesn't do that.

The closest you can get with one SQL statement is to output exactly one column containing XML or JSON. The program calling the database would then be responsible for turning that result into rows and columns.

Another alternative is to execute one SELECT to get the column names and generate a second SELECT to get the result you want. I wrote a function to help do that: https://stewashton.wordpress.com/2018/05/30/improved-pivot-function/

I'm not going to demonstrate any of these alternatives because they don't directly answer your question. There is no direct answer to your question as asked.