Dynamic pivot in oracle sql

Question

... pivot (sum(A) for B in (X))

Now B is of datatype varchar2 and X is a string of varchar2 values separated by commas.
Values for X are select distinct values from a column(say CL) of same table. This way pivot query was working.

But the problem is that whenever there is a new value in column CL I have to manually add that to the string X.

I tried replacing X with select distinct values from CL. But query is not running.
The reason I felt was due to the fact that for replacing X we need values separated by commas.
Then i created a function to return exact output to match with string X. But query still doesn't run.
The error messages shown are like "missing righr parantheses", "end of file communication channel" etc etc.
I tried pivot xml instead of just pivot, the query runs but gives vlaues like oraxxx etc which are no values at all.

Maybe I am not using it properly.
Can you tell me some method to create a pivot with dynamic values?

Answer 1

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.