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