SQL 带有子查询的 Oracle PIVOT 子句示例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8759939/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Example of an Oracle PIVOT clause with subquery
提问by Lukas Eder
Oracle's definition of the PIVOT clausespecifies that there is a possibility to define a subquery in the INclause. 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 expressionerror. Unfortunately, errors from this new PIVOTclause are usually rather cryptic. Can anyone give me a good example of how a subquery can be used in the INclause of the PIVOTclause?
但是，我收到了一个ORA-00936: Missing expression错误。不幸的是，这个新PIVOT条款的错误通常相当神秘。谁能给我一个很好的例子来说明如何在IN子句的PIVOT子句中使用子查询？
采纳答案by Lukas Eder
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. [...]
subquery 子查询仅与 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
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#CHDFAFIE
回答by Harshita
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.
我有类似的要求。我通过 pl sql 编写了一个动态 sql 并将其添加到 pivot IN 子句中实现了这一点。当然pivot查询也是动态sql。但是在普通的pivot子句中这是不可能的，使用sql。
