Oracle PIVOT

A common requirement for queries is to turn rows into columns, or the other way around.

In Excel, we can do this using TRANSPOSE, a bit of patience & know-how, and ctrl+shift+enter.

In Oracle, if we have aggregate data displaying months by row, the old way was to use a bunch of DECODEs (or similar)

SELECT t.name
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'jan',1,0)) jan
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'feb',1,0)) feb
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'mar',1,0)) mar
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'apr',1,0)) apr
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'may',1,0)) may
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'jun',1,0)) jun
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'jul',1,0)) jul
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'aug',1,0)) aug
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'sep',1,0)) sep
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'oct',1,0)) oct
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'nov',1,0)) nov
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'dec',1,0)) dec
FROM events e, bookings b, resources r, resource_types t
WHERE e.event_no = b.event_no
AND r.code = b.resource_code
AND r.type_code = t.code
GROUP BY t.name;

NAME                 JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
-------------------- --- --- --- --- --- --- --- --- --- --- --- ---
Catering               0   0   2   1   0   0   0   0   0   0   0   0 
Stationary             0   0   1   1   0   0   0   0   0   0   0   0 
Video equipment        0   0   1   1   1   0   0   0   1   0   0   0 
Audio equipment        0   0   0   0   0   0   0   0   0   1   0   0 
Computer equipment     0   0   1   0   0   0   0   0   0   0   0   0 
Locations              0   0   2   2   2   1   1   1   1   1   0   0 

6 rows selected 

Oracle 11g introduced pivot queries.

SELECT * FROM
 ( SELECT COUNT(*) c, t.name, TO_CHAR(start_date,'mon') mth
   FROM events e, bookings b, resources r, resource_types t
   WHERE e.event_no = b.event_no
   AND r.code = b.resource_code
   AND r.type_code = t.code
   GROUP BY t.name, to_char(start_date,'mon')
)
PIVOT
 (SUM(c) -- Add up all my counts
  FOR mth -- Transposing the months
    IN ('jan' as jan
             ,'feb','mar','apr','may','jun'
       ,'jul','aug','sep','oct','nov','dec')
);

NAME                 JAN 'feb' 'mar' 'apr' 'may' 'jun' 'jul' 'aug' 'sep' 'oct' 'nov' 'dec'
-------------------- --- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
Catering                           2     1                                                 
Stationary                         1     1                                                 
Video equipment                    1     1     1                       1                   
Audio equipment                                                              1             
Computer equipment                 1                                                       
Locations                          2     2     2     1     1     1     1     1             

6 rows selected 

Note line 12 where we can provide column aliases to the fresh output.

As with most esoteric SQL functions, there are quite a few good examples on the web that I'm not out to emulate - the prime purpose of this post was to help remind me what's going on.
That being said, Tim Hall and Arup Nanda have the most concise articles.

I particularly like Lucas Jellema's example linked by Arup using

select value
from
(
    (
        select
            'a' v1,
            'e' v2,
            'i' v3,
            'o' v4,
            'u' v5
        from dual
    )
    unpivot
    (
        value
        for value_type in
            (v1,v2,v3,v4,v5)
    )
)
/

I wonder if that might be an interesting catalyst in some future queries
Update August 2014 - yes it did! -> Unpivot