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;
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