Skip to Main Content
  • Questions
  • Is it possible to use dynamic query in PIVOT in clause?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Pradeep.

Asked: August 14, 2012 - 2:41 am UTC

Last updated: August 15, 2012 - 9:51 am UTC

Version: 11.2.0.2.0

Viewed 50K+ times! This question is

You Asked

With deep as
(
SELECT
deptno,job,sum(sal) sum
from emp
WHERE
1=1
GROUP BY DEPTNO,JOB
ORDER BY DEPTNO,JOB
)

SELECT * FROM DEEP
PIVOT
(
SUM(SUM)
for job
in (
'CLERK','SALESMAN','PRESIDENT','MANAGER','ANALYST')
)
;


-- Above query works absolutely fine but I wanted to make the list in 'IN' clause as dynamic say to fetch data from a table.. It got errored out, wanted to know is it possible to use such a query in Pivot clause?

Thanks,
Pradeep

and Tom said...

the short answer is "no"

Longer answers are:


See
http://www.oracle.com/technetwork/issue-archive/2012/12-jul/o42asktom-1653097.html
the section on Dynamic Pivot


Although there is a way to do it 'dynamically' with PLSQL and an object type:
http://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

I do not necessarily recommend that last approach in general, there is some pretty large parsing overhead involved unfortunately and I do not see a way to avoid that. If the query is going to be executed on a regular basis, that could have some impact.

But as always - benchmark

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Dynamic query pivot IN clause - using XML

Rajeshwaran Jeyabal, August 15, 2012 - 10:25 am UTC

@pradeep, checkit out if this help us, using pivot XML you can have dynamic query in PIVOT in clause.

rajesh@ORA11GR2>
rajesh@ORA11GR2> select *
  2  from (
  3  select deptno,job
  4  from emp
  5        )
  6  pivot
  7  ( count(*)
  8    for deptno in (10,20,30) )
  9  /

JOB               10         20         30
--------- ---------- ---------- ----------
CLERK              1          2          1
SALESMAN           0          0          4
PRESIDENT          1          0          0
MANAGER            1          1          1
ANALYST            0          2          0

Elapsed: 00:00:00.21
rajesh@ORA11GR2>
rajesh@ORA11GR2> select *
  2  from (
  3  select deptno,job
  4  from emp
  5        )
  6  pivot
  7  ( count(*)
  8    for deptno in (select deptno from dept) )
  9  /
  for deptno in (select deptno from dept) )
                 *
ERROR at line 8:
ORA-00936: missing expression


Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> column deptno_xml format a30 trunc;
rajesh@ORA11GR2> select *
  2  from (
  3  select deptno,job
  4  from emp
  5        )
  6  pivot XML
  7  ( count(*)
  8    for deptno in (select deptno from dept) )
  9  /

JOB       DEPTNO_XML
--------- ------------------------------
ANALYST   <PivotSet><item><column name =
CLERK     <PivotSet><item><column name =
MANAGER   <PivotSet><item><column name =
PRESIDENT <PivotSet><item><column name =
SALESMAN  <PivotSet><item><column name =

Elapsed: 00:00:00.09
rajesh@ORA11GR2>

anydata

Laurent Schneider, August 15, 2012 - 11:15 am UTC

Truely amazing usage of anydata from Anton !

average review

A reader, April 25, 2017 - 9:45 am UTC