Oracle Dynamic Pivoting

Question

I have the below table. I need to create columns based off the column CCL. The values in column CCL are unknown. I'm not sure where to begin here. Any help would be appreciated.

TABLEA

ID    CCL    Flag
1     john     x
1     adam     x
1     terry
1     rob      x
2     john     x

Query:

SELECT *
FROM TABLEA

Output:

ID  John  Adam  Terry  Rob
 1    x     x           x
 2    x       

Answer 1

Using dynamic sql for a result where the columns are unknown at the time of executing is a bit of a hassle in Oracle compared to certain other RDMBS.

Because the record type for the output is yet unknown, it can't be defined beforehand.

In Oracle 11g, one way is to use a nameless procedure that generates a temporary table with the pivoted result.

Then select the results from that temporary table.

declare
  v_sqlqry clob;
  v_cols clob;
begin
  -- Generating a string with a list of the unique names
  select listagg(''''||CCL||''' as "'||CCL||'"', ', ') within group (order by CCL)
  into v_cols
  from 
  (
    select distinct CCL
    from tableA
  );

  -- drop the temporary table if it exists
  EXECUTE IMMEDIATE 'DROP TABLE tmpPivotTableA';
  EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF;

  -- A dynamic SQL to create a temporary table 
  -- based on the results of the pivot
  v_sqlqry := '
    CREATE GLOBAL TEMPORARY TABLE tmpPivotTableA
    ON COMMIT PRESERVE ROWS AS
    SELECT * 
    FROM (SELECT ID, CCL, Flag FROM TableA) src 
    PIVOT (MAX(Flag) FOR (CCL) IN ('||v_cols||')) pvt';

  -- dbms_output.Put_line(v_sqlqry); -- just to check how the sql looks like
  execute immediate v_sqlqry;

end;
/

select * from tmpPivotTableA;

Returns:

ID  adam john rob terry
--  ---- ---- --- -----
1   x    x    x
2        x      

You can find a test on db<>fiddle here

In Oracle 11g, another cool trick (created by Anton Scheffer) to be used can be found in this blog. But you'll have to add the pivot function for it.
The source code can be found in this zip

After that the SQL can be as simple as this:

select * from 
table(pivot('SELECT ID, CCL, Flag FROM TableA'));

You'll find a test on db<>fiddle here

Answer 2

Oracle must know all the column in select list on PARSING stage.

This has a couple of consequences

1. It's not possible for Oracle to change the column list of the query without re-parsing it. Regardless what is supposed to impact that - whether it's distinct list of values in some column or something else. In other words you cannot expect Oracle to add new columns to output if you added new value to CCL column in your example.

2. In each and every query you must specify explicitly all the columns in select list unless you use "*" with table alias. If you use "*" then Oracle gets column list from metadata and if you modify metadata (i.e. run DDL on a table) then Oracle re-parses query.

So the best option to deal with "Dynamic Pivoting" is to pivot and format result in the UI. However, there are still some options in database which you may want to consider.

Generating XML with pivoted result and parsing it.

Do pivot for XML and then parse results. In this case, eventually, you have to specify pivoted columns one way or another.

create table tablea(id, ccl, flag) as
(
  select 1, 'john', 'x' from dual
  union all select 1, 'adam', 'x' from dual
  union all select 1, 'terry', null from dual
  union all select 1, 'rob', 'x' from dual
  union all select 2, 'john', 'x' from dual
);

In below example you do NOT have to provide list of the values for CCL, the only literals you specify are: pivoted expression (FLAG) and column used for pivoting (CCL).

SQL> select id, x.*
  2  from tablea t
  3  pivot xml (max(flag) flag for ccl in(any))
  4  -- parsing output
  5  , xmltable('/PivotSet' passing ccl_xml
  6             columns
  7               name1 varchar2(30) path '/PivotSet/item[1]/column[@name="CCL"]/text()',
  8               value1 varchar2(30) path '/PivotSet/item[1]/column[@name="FLAG"]/text()',
  9               name2 varchar2(30) path '/PivotSet/item[2]/column[@name="CCL"]/text()',
 10               value2 varchar2(30) path '/PivotSet/item[2]/column[@name="FLAG"]/text()',
 11               name3 varchar2(30) path '/PivotSet/item[3]/column[@name="CCL"]/text()',
 12               value3 varchar2(30) path '/PivotSet/item[3]/column[@name="FLAG"]/text()',
 13               name4 varchar2(30) path '/PivotSet/item[4]/column[@name="CCL"]/text()',
 14               value4 varchar2(30) path '/PivotSet/item[4]/column[@name="FLAG"]/text()') x;

        ID NAME1 VALUE NAME2 VALUE NAME3 VALUE NAME4 VALUE
---------- ----- ----- ----- ----- ----- ----- ----- -----
         1 adam  x     john  x     rob   x     terry
         2 john  x

You may have noticed 2 important details

• In fact, each pivoted column is represented using two columns in result - one for caption and one for value

• Names are ordered so you cannot preserver order like in your example ('john', 'adam', 'terry', 'rob'), moreover one column may represent different names like NAME1 represents values for 'adam' in first row and 'john' in second row.

It's possible to use only indices to get the same output.

select id, x.*
from tablea
pivot xml (max(flag) flag for ccl in(any))
-- parsing output
, xmltable('/PivotSet' passing ccl_xml
           columns
             name1 varchar2(30) path '/PivotSet/item[1]/column[1]',
             value1 varchar2(30) path '/PivotSet/item[1]/column[2]',
             name2 varchar2(30) path '/PivotSet/item[2]/column[1]',
             value2 varchar2(30) path '/PivotSet/item[2]/column[2]',
             name3 varchar2(30) path '/PivotSet/item[3]/column[1]',
             value3 varchar2(30) path '/PivotSet/item[3]/column[2]',
             name4 varchar2(30) path '/PivotSet/item[4]/column[1]',
             value4 varchar2(30) path '/PivotSet/item[4]/column[2]') x;

But still there are two columns for each pivoted column in the output.

Below query returns exactly the same data as in your example

SQL> select id, x.*
  2  from tablea
  3  pivot xml (max(flag) flag for ccl in(any))
  4  -- parsing output
  5  , xmltable('/PivotSet' passing ccl_xml
  6             columns
  7               john varchar2(30) path '/PivotSet/item[column="john"]/column[2]',
  8               adam varchar2(30) path '/PivotSet/item[column="adam"]/column[2]',
  9               terry varchar2(30) path '/PivotSet/item[column="terry"]/column[2]',
 10               rob varchar2(30) path '/PivotSet/item[column="rob"]/column[2]') x;

        ID JOHN  ADAM  TERRY ROB
---------- ----- ----- ----- -----
         1 x     x           x
         2 x

But wait... all the values for CCL are specified in the query. This is because column caption cannot depend on the data in the table. So what is the point in pivoting for XML if you could have just hardcoded all values in for clause with the same success? One of the ideas is that Oracle SQL engine transposes query result and the tool which displays output just has to properly parse XML. So you split pivoting logic into two layers. XML parsing can be done outside SQL, say, in your application.

ODCI table interface

There is already a link in another answer to Anton's solution. You can also check an example here. And, of course, it's explained in detail in Oracle Documentation.

Polymorphic Table Functions

One more advanced technology has been introduces in Oracle 18 - Polymorphic Table Functions. But again, you should not expect that column list of your query will change after you added new value to CCL. It can change only after re-parsing. There is a way to force hard parse before each excution, but that is another topic.

Dynamic SQL

Finally, as also already pointed out in the comments, you can use good old DSQL. First step - generate SQL statement based on the table contents. Second step - execute it.

SQL> var rc refcursor
SQL> declare
  2    tmp clob;
  3    sql_str clob := 'select * from tablea pivot (max(flag) for ccl in ([dynamic_list]))';
  4  begin
  5    select listagg('''' || ccl || ''' as ' || ccl, ',') within group(order by max(ccl))
  6      into tmp
  7      from tablea
  8     group by ccl;
  9    open :rc for replace(sql_str, '[dynamic_list]', tmp);
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> print rc

        ID ADAM  JOHN  ROB   TERRY
---------- ----- ----- ----- -----
         1 x     x     x
         2       x