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?
Yes I think so. It is easy to do a pivot like this with a
MAX
aggregate:Otherwise you have to do a case statement inside the a max aggregate. Like this:
This is almost the same thing as doing the
PIVOT
. But I would prefer doing thePIVOT
over theCASE WHEN MAX
..