Oracle Dynamic Pivoting
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
Oracle must know all the column in select list on PARSING stage.
This has a couple of consequences
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.
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