ORACLE 上的 PIVOT/GROUP BY 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9330202/
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
PIVOT / GROUP BY issue on ORACLE
提问by Ajantis
My first query where I got problem here : Tricky GROUP BY issue on ORACLEis now definitely resolved.
我在这里遇到问题的第一个查询：ORACLE 上的 Tricky GROUP BY 问题现在肯定已解决。
However I have a new question. I try to transform it, one more time to have now this output :
不过我有一个新问题。我尝试转换它，再一次得到这个输出：
| EMAIL | WIFI | ...
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Yes | 20 | 24 | ...
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
No | 4 | 0 | ...
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Unknown | 1 | 1 | ...
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Here the data to help you to build such output. I tried to use again unpivot / pivot with the query that René gave me in the resolved issue I quote, but unfortunately I got the error that "ORA-56901: non-constant expression is not allowed for pivot|unpivot values" sighh...
这里的数据可帮助您构建此类输出。我尝试再次使用 unpivot/pivot 与 René 在我引用的已解决问题中给我的查询，但不幸的是我收到错误“ORA-56901：pivot|unpivot 值不允许使用非常量表达式”叹息.. .
with
count_table as (
select 1001 device_id, 4 quantity from dual union all
select 1002 device_id, 20 quantity from dual union all
select 1003 device_id, 1 quantity from dual
),
device_table as (
select 1001 id, 'Yes' wifi, 'No' email, 'No' bluetooth from dual union all
select 1002 id, 'Yes' wifi, 'Yes' email, 'No' bluetooth from dual union all
select 1003 id, 'Unknown' wifi, 'Unknown' email, 'Yes' bluetooth from dual
)
Maybe there is a simpler solution for this ? I definitely need to read a book about relational DB:)
也许有一个更简单的解决方案？我绝对需要阅读一本关于关系数据库的书:)
采纳答案by pratik garg
it is looking very simple after referring your previous post.. please try the below query for that ...
参考您之前的帖子后，它看起来非常简单..请尝试以下查询...
with
count_table as (
select 1001 device_id, 4 quantity from dual union all
select 1002 device_id, 20 quantity from dual union all
select 1003 device_id, 1 quantity from dual
),
device_table as (
select 1001 id, 'Yes' wifi, 'No' email, 'No' bluetooth from dual union all
select 1002 id, 'Yes' wifi, 'Yes' email, 'No' bluetooth from dual union all
select 1003 id, 'Unknown' wifi, 'Unknown' email, 'Yes' bluetooth from dual
)
----------------------------------------
select * from (
select
feature,
yes_no_unknown,
sum(quantity) quantity
from
count_table c join
device_table d on c.device_id = d.id
unpivot ( yes_no_unknown
for feature in (wifi, email, bluetooth)
)
group by
feature,
yes_no_unknown
)
pivot ( sum (quantity)
-- only this line I have changed ..
for feature in ('WIFI' as Wifi, 'EMAIL' as Email, 'BLUETOOTH' as Bluetooth)
);
回答by Szilard Barany
If the number of the columns of the output table is flexible, you can probably use some procedural solution; PL/SQL or Java.
如果输出表的列数是灵活的，你可能可以使用一些程序解决方案；PL/SQL 或 Java。
In PL/SQL you can create a two dimensional collection and populate it and then print it out. Of you can create/generate a dynamic SQL query using dbms_sqlpackage.
在 PL/SQL 中，您可以创建一个二维集合并填充它，然后将其打印出来。您可以使用dbms_sql包创建/生成动态 SQL 查询。
