Oracle Dynamic Pivoting

ghz 1years ago ⋅ 1381 views

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

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](https://technology.amis.nl/2006/05/24/dynamic-sql- pivoting-stealing-antons-thunder/). But you'll have to add the pivot function for it.
The source code can be found [in this zip](https://technology.amis.nl/wp- content/uploads/images/antonsPivoting.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

#oracle