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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Pradeep.

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

Answered by: Tom Kyte - Last updated: August 15, 2012 - 9:51 am UTC

Category: Database - 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 we 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

and you rated our response

  (3 ratings)

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

Reviews

Dynamic query pivot IN clause - using XML

August 15, 2012 - 10:25 am UTC

Reviewer: Rajeshwaran Jeyabal

@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

August 15, 2012 - 11:15 am UTC

Reviewer: Laurent Schneider from switzerland

Truely amazing usage of anydata from Anton !

average review

April 25, 2017 - 9:45 am UTC

Reviewer: A reader