title: Example of an Oracle PIVOT clause with subquery
body: <p>Oracle's <a href="http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#sthref9288" rel="noreferrer">definition of the PIVOT clause</a> specifies that there is a possibility to define a subquery in the <code>IN</code> clause. A fictional example of what I would imagine this to be is this</p>
  
  <pre><code>... PIVOT (AVG(salary) FOR (company) IN (SELECT DISTINCT company FROM companies))
  </code></pre>
  
  <p>With that, however, I get an <code>ORA-00936: Missing expression</code> error. Unfortunately, errors from this new <code>PIVOT</code> clause are usually rather cryptic. Can anyone give me a good example of how a subquery can be used in the <code>IN</code> clause of the <code>PIVOT</code> clause?</p>