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?
Oracle PIVOT with subqueryWhen you use a subquery in the pivot_in_clause , Oracle uses all values returned by the subquery for pivoting. Note that the subquery must return a list of unique values. Otherwise, Oracle will raise a run-time error.
The PIVOT clause is new for Oracle Database 11g and enables you to flip the rows into columns in the output from a query, and, at the same time,allow you to run an aggregation function on the data. PIVOT is especially useful to look at overall trends in large amounts of data.
Hi, Order by clause does not works inside a Sub-Query.No use of giving ORDER BY clause inside the sub query. Subquery gives values to the outer query and outer query only orders the value based on the order by clause.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With