在Oracle中通过汇总来旋转/旋转表 [英] Rotate/pivot table with aggregation in Oracle
问题描述
我想在Oracle 11g中旋转一个表.枢纽选项需要汇总. 这是我的原始表格:
I'd like to rotate a table in Oracle 11g. The pivot option requires an aggregation. This is my original table:
project | attribute | value
===========================
'cust1' | 'foo' | '4'
'cust2' | 'bar' | 'tbd'
'cust3 | 'baz' | '2012-06-07'
'cust1' | 'bar' | 'tdsa'
'cust4' | 'foo' | '22'
'cust4' | 'baz' | '2013-01-01'
旋转后,表格应如下所示:
After pivoting, the table should look like this:
project | foo | bar | baz
=========================
'cust1' | '4' |'tdba'| NULL
'cust2' | NULL|'tbd' | NULL
'cust3' | NULL| NULL | '2012-06-07'
'cust4' | '22'| NULL | '2013-01-01'
现在,如您所见,分组应该在项目列上进行.无需折叠或计算值.只需旋转即可. 那么,枢轴选择正确的事情了吗?
Now, as you can see, the grouping should happen over the project column. No values need to be collapsed or calcucation. A mere rotation is necessary. So, is the pivot select the right thing to do?
推荐答案
是的.使用MAX
聚合可以很容易地进行这样的数据透视:
Yes I think so. It is easy to do a pivot like this with a MAX
aggregate:
SELECT
*
FROM
(
SELECT
project,
attribute,
value
FROM
table1
) AS SourceTable
PIVOT
(
MAX(value)
FOR attribute IN ([foo],[bar],[baz])
) AS pvt
否则,您必须在最大聚合内执行一个case语句.像这样:
Otherwise you have to do a case statement inside the a max aggregate. Like this:
SELECT
MAX(CASE WHEN attribute='foo' THEN value ELSE NULL END) AS foo,
MAX(CASE WHEN attribute='bar' THEN value ELSE NULL END) AS bar,
MAX(CASE WHEN attribute='baz' THEN value ELSE NULL END) AS baz,
project
FROM
table1
GROUP BY
project
这几乎与PIVOT
相同.但是我更喜欢使用PIVOT
而不是CASE WHEN MAX
..
This is almost the same thing as doing the PIVOT
. But I would prefer doing the PIVOT
over the CASE WHEN MAX
..
这篇关于在Oracle中通过汇总来旋转/旋转表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!