带有子查询的 Oracle PIVOT 子句示例
Oracle 的PIVOT 子句的定义指定可以在 IN 子句中定义子查询。我想象的一个虚构的例子是这样的
... PIVOT (AVG(salary) FOR (company) IN (SELECT DISTINCT company FROM companies))
，但是，我收到一个ORA-00936：缺少表达式错误。不幸的是，这个新的 PIVOT 子句中的错误通常相当神秘。谁能给我一个很好的例子来说明如何在 PIVOT 子句的 IN 子句中使用子查询？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
显然，我懒得读到文档的结尾......更进一步，文档指出：
这将起作用
请参阅完整文档
http://docs.oracle.com/cd/B28359_01/server.111/b28286/ statements_10002.htm#CHDFAFIE
Apparently, I was too lazy to read to the end of the documentation... Further down, the documentation states:
This will work
See the full documentation
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#CHDFAFIE
我有类似的要求。我通过 pl sql 编写了一个动态 sql 并将其添加到数据透视 IN 子句中来实现这一点。当然pivot查询也是动态sql。但在普通的数据透视子句中，使用 sql 这是不可能的。
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.