반응형
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)
Oracle 11g introduced pivot queries.
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
I wonder if that might be an interesting catalyst in some future queries
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)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 |
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)) decFROM 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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 |
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 |
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 |
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) ) ) / |
출처 : http://www.grassroots-oracle.com/2012/05/oracle-pivot.html
