带有子查询的Oracle PIVOT子句的示例

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

Oracle对PIVOT子句的定义指定可以在IN子句中定义子查询。我想象的一个虚构的例子就是这个

... 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?

然而，有了这个，我得到一个ORA-00936：缺少表达式错误。不幸的是，这个新的PIVOT条款的错误通常是相当神秘的。谁能给我一个很好的例子，说明子查询如何在PIVOT子句的IN子句中使用？

1 个解决方案

#1

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

子查询子查询仅与XML关键字一起使用。指定子查询时，子查询找到的所有值都用于旋转。 [...]

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

#1