... 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?
For later readers, here is another solution https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/
allowing a query like
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:
It should look like this:
You can also have a subquery instead of the
ANY
keyword:Here is a sqlfiddle demo
USE DYNAMIC QUERY
Test code is below
I used the above method (Anton PL/SQL custom function pivot()) and it done the job! As I am not a professional Oracle developer, these are simple steps I've done:
1) Download the zip package to find pivotFun.sql in there. 2) Run once the pivotFun.sql to create a new function 3) Use the function in normal SQL.
Just be careful with dynamic columns names. In my environment I found that column name is limited with 30 characters and cannot contain a single quote in it. So, my query is now something like this:
Works well with up to 1m records.
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;
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.
Your string will look like:
Now use the String statement in your PIVOT query.
Here is the Output:
There are limitations though. You can only concatenate a string up to 4000 bytes.
I am not exactly going to give answer for the question OP has asked, instead I will be just describing how dynamic pivot can be done.
Here we have to use dynamic sql, by initially retrieving the column values into a variable and passing the variable inside dynamic sql.
EXAMPLE
Consider we have a table like below.
If we need to show the values in the column
YR
as column names and the values in those columns fromQTY
, then we can use the below code.RESULT
If required, you can also create a temp table and do a select query in that temp table to see the results. Its simple, just add the
CREATE TABLE TABLENAME AS
in the above code.