Dynamic pivot in oracle sql

You cannot put a dynamic statement in the PIVOT's IN statement without using PIVOT XML, which outputs some less than desirable output. However, you can create an IN string and input it into your statement.

First, here is my sample table;

  myNumber    myValue myLetter---------- ---------- --------         1          2 A                 1          4 B                 2          6 C                 2          8 A                 2         10 B                 3         12 C                 3         14 A      

First setup the string to use in your IN statement. Here you are putting the string into "str_in_statement". We are using COLUMN NEW_VALUE and LISTAGG to setup the string.

clear columnsCOLUMN temp_in_statement new_value str_in_statementSELECT DISTINCT     LISTAGG('''' || myLetter || ''' AS ' || myLetter,',')        WITHIN GROUP (ORDER BY myLetter) AS temp_in_statement     FROM (SELECT DISTINCT myLetter FROM myTable);

Your string will look like:

'A' AS A,'B' AS B,'C' AS C

Now use the String statement in your PIVOT query.

SELECT * FROM     (SELECT myNumber, myLetter, myValue FROM myTable)    PIVOT (Sum(myValue) AS val FOR myLetter IN (&str_in_statement));

Here is the Output:

  MYNUMBER      A_VAL      B_VAL      C_VAL---------- ---------- ---------- ----------         1          2          4                     2          8         10          6          3         14                    12 

There are limitations though. You can only concatenate a string up to 4000 bytes.

You can't put a non constant string in the IN clause of the pivot clause.
You can use Pivot XML for that.

From documentation:

subquery A subquery is used only in conjunction with the XML keyword. When you specify a subquery, all values found by the subquery are used for pivoting

It should look like this:

select xmlserialize(content t.B_XML) from t_aapivot xml(sum(A) for B in(any)) t;

You can also have a subquery instead of the ANY keyword:

select xmlserialize(content t.B_XML) from t_aapivot xml(sum(A) for B in (select cl from t_bb)) t;

Here is a sqlfiddle demo

For later readers, here is another solutionhttps://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

allowing a query like

select * from table( pivot(  'select deptno,  job, count(*) c from scott.emp group by deptno,job' ) )