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
