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

출처 : http://www.grassroots-oracle.com/2012/05/oracle-pivot.html