Dynamic Pivot in Oracle’s 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 columns
COLUMN temp_in_statement new_value str_in_statement
SELECT 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.