Skip to Main Content
  • Questions

  • Is it possible to use dynamic query in PIVOT in clause?

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

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


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.