Dynamic pivot in oracle sql
Solution 1
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_aa
pivot 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_aa
pivot xml(
sum(A) for B in (select cl from t_bb)
) t;
Solution 2
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.
Solution 3
For later readers, here is another solution https://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' ) )
Solution 4
USE DYNAMIC QUERY
Test code is below
-- DDL for Table TMP_TEST
--------------------------------------------------------
CREATE TABLE "TMP_TEST"
( "NAME" VARCHAR2(20),
"APP" VARCHAR2(20)
);
/
SET DEFINE OFF;
Insert into TMP_TEST (NAME,APP) values ('suhaib','2');
Insert into TMP_TEST (NAME,APP) values ('suhaib','1');
Insert into TMP_TEST (NAME,APP) values ('shahzad','3');
Insert into TMP_TEST (NAME,APP) values ('shahzad','2');
Insert into TMP_TEST (NAME,APP) values ('shahzad','5');
Insert into TMP_TEST (NAME,APP) values ('tariq','1');
Insert into TMP_TEST (NAME,APP) values ('tariq','2');
Insert into TMP_TEST (NAME,APP) values ('tariq','6');
Insert into TMP_TEST (NAME,APP) values ('tariq','4');
/
CREATE TABLE "TMP_TESTAPP"
( "APP" VARCHAR2(20)
);
SET DEFINE OFF;
Insert into TMP_TESTAPP (APP) values ('1');
Insert into TMP_TESTAPP (APP) values ('2');
Insert into TMP_TESTAPP (APP) values ('3');
Insert into TMP_TESTAPP (APP) values ('4');
Insert into TMP_TESTAPP (APP) values ('5');
Insert into TMP_TESTAPP (APP) values ('6');
/
create or replace PROCEDURE temp_test(
pcursor out sys_refcursor,
PRESULT OUT VARCHAR2
)
AS
V_VALUES VARCHAR2(4000);
V_QUERY VARCHAR2(4000);
BEGIN
PRESULT := 'Nothing';
-- concating activities name using comma, replace "'" with "''" because we will use it in dynamic query so "'" can effect query.
SELECT DISTINCT
LISTAGG('''' || REPLACE(APP,'''','''''') || '''',',')
WITHIN GROUP (ORDER BY APP) AS temp_in_statement
INTO V_VALUES
FROM (SELECT DISTINCT APP
FROM TMP_TESTAPP);
-- designing dynamic query
V_QUERY := 'select *
from ( select NAME,APP
from TMP_TEST )
pivot (count(*) for APP in
(' ||V_VALUES|| '))
order by NAME' ;
OPEN PCURSOR
FOR V_QUERY;
PRESULT := 'Success';
Exception
WHEN OTHERS THEN
PRESULT := SQLcode || ' - ' || SQLERRM;
END temp_test;
Solution 5
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:
SELECT
*
FROM
table(
pivot('
SELECT DISTINCT
P.proj_id,
REPLACE(substr(T.UDF_TYPE_LABEL, 1, 30), '''''''','','') as Attribute,
CASE
WHEN V.udf_text is null and V.udf_date is null and V.udf_number is NOT null THEN to_char(V.udf_number)
WHEN V.udf_text is null and V.udf_date is NOT null and V.udf_number is null THEN to_char(V.udf_date)
WHEN V.udf_text is NOT null and V.udf_date is null and V.udf_number is null THEN V.udf_text
ELSE NULL END
AS VALUE
FROM
project P
LEFT JOIN UDFVALUE V ON P.proj_id = V.proj_id
LEFT JOIN UDFTYPE T ON V.UDF_TYPE_ID = T.UDF_TYPE_ID
WHERE
P.delete_session_id IS NULL AND
T.TABLE_NAME = ''PROJECT''
')
)
Works well with up to 1m records.
prabhakar
Updated on July 05, 2022Comments
-
prabhakar almost 2 years
... 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? -
prabhakar about 11 yearshi your method is working actually but output i am getting in xml format. can i get output as a table with rows and columns ?
-
A.B.Cade about 11 yearsAFAIK, not dynamically... But how do you intend to use a result which you don't know its structure ?
-
lourdh about 8 yearswhile trying this i am getting below oracle error: ORA-56900: bind variable is not supported inside pivot|unpivot operation
-
Ram over 7 yearsHow to implement this in Oracle procedure? pls give an example
-
user3141985 over 5 yearsDoes this solution works in Oracle 11g? As I am unable to execute subquery in this format.
-
Scott over 5 yearsGiven the dates involved, then yes, it should. 12c wasn't around back then. Double check privileges.
-
Hamlet Mendez almost 5 yearshow can I put the first select query inside the IN clause without a variable?
-
अक्षय परूळेकर almost 5 yearsError report - ORA-06550: line 5, column 74: PL/SQL: ORA-00923: FROM keyword not found where expected ORA-06550: line 5, column 3: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:
-
StewS2 about 3 yearsI was hoping to use it against another dynamic SQL, but you have a good point about knowing the structure.
-
L.Newell about 2 yearsThis answer is genius and gave me exactly what i needed. Thank you for sharing your expertise.
-
Nad almost 2 yearsSarath, can u help me on this stackoverflow.com/questions/72413894/…