Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Example of an Oracle PIVOT clause with subquery

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?

like image 467
Lukas Eder Avatar asked Jan 06 '12 15:01

Lukas Eder


People also ask

Can we use subquery in PIVOT in Oracle?

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.

What is PIVOT in Oracle with example?

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.

Can we use Order By clause in subquery in Oracle?

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.


2 Answers

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

like image 152
Lukas Eder Avatar answered Oct 01 '22 19:10

Lukas Eder


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.

like image 39
Harshita Avatar answered Oct 01 '22 20:10

Harshita