在 Oracle 中使用聚合旋转/透视表

2022-01-22 00:00:00 pivot pivot-table sql 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

否则，您必须在 a max 聚合中执行 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..

相关文章