4

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?

Has QUIT--Anony-Mousse
  • 76,138
  • 12
  • 138
  • 194
Michael-O
  • 18,123
  • 6
  • 55
  • 121
  • Why did you tag this [tag:data-mining]? I see no data mining questions here, no statistics involved. – Has QUIT--Anony-Mousse Jun 07 '12 at 22:02
  • I presumed that pivot is used primary in data-mining. – Michael-O Jun 08 '12 at 07:06
  • 2
    Not at all. Data-mining is statistics, and mostly done way outside of SQL databases, because it's a read-only analysis that does not need any ACID guarantees. https://en.wikipedia.org/wiki/Pivot_table does not even mention "mining". It is probably used in business intelligence and *data drilling*, but not in actual data mining. It is a "spreadsheet" type of operation business users tend to understand well. – Has QUIT--Anony-Mousse Jun 08 '12 at 07:21

1 Answers1

7

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

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

This is almost the same thing as doing the PIVOT. But I would prefer doing the PIVOT over the CASE WHEN MAX..

Arion
  • 31,011
  • 10
  • 70
  • 88
  • How is `MAX` supposed to work here? As you can see, value is of type `VARCHAR`- – Michael-O Jun 07 '12 at 09:52
  • If you have duplicates of for example `foo` for the same project and with different value then you need to take one of them. You could use `min` as well. It will take the `max` for on the varchar value. – Arion Jun 07 '12 at 09:56
  • The (project,attribute) pair is unique. So no data loss will happen. I have evaluated this on a table with > 100 000 records. You query works as desired. Thanks! – Michael-O Jun 07 '12 at 16:30