32

Oracle's definition of the PIVOT clause specifies that there is a possibility to define a subquery in the IN clause. A fictional example of what I would imagine this to be is this

... PIVOT (AVG(salary) FOR (company) IN (SELECT DISTINCT company FROM companies))

With that, however, I get an ORA-00936: Missing expression error. Unfortunately, errors from this new PIVOT clause are usually rather cryptic. Can anyone give me a good example of how a subquery can be used in the IN clause of the PIVOT clause?

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509

2 Answers2

27

Apparently, I was too lazy to read to the end of the documentation... Further down, the documentation states:

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. [...]

This will work

PIVOT XML (AVG(salary) FOR (company) IN (SELECT DISTINCT company FROM companies))

See the full documentation

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#CHDFAFIE

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • 7
    So I saw that this returns output in XML format. Any idea how to get normal output(table) as plain PIVOT would give? – tumchaaditya Jan 02 '14 at 23:07
  • @tumchaaditya: I'm not sure if that's possible. But to be sure, feel free to ask a new Stack Overflow question. And link to it from here, I'm curious, myself... – Lukas Eder Jan 03 '14 at 07:32
  • 1
    I ended up constructing the results of that subquery dynamicaly outside the SQL with pivot. I mean I run the subquery separately and pipe that output to the main query – tumchaaditya Jan 03 '14 at 17:05
2

i had a similar requirement. I achieved this via pl sql wrote a dynamic sql and added it to the pivot IN clause. Ofcourse pivot query was also a dynamic sql. But in normal pivot clause this is not possible, using sql.

Harshita
  • 21
  • 1
  • 5
    Please edit your answer to include more detail. Simply giving a general outline of your solution is not helpful for future readers. – mypetlion Sep 05 '18 at 18:06