11G
Pivot tables
/ 1
- Details
- Parent Category: Articles
- Created on Monday, 22 March 2010 21:17
- Last Updated on Monday, 12 March 2012 13:54
- Published on Monday, 22 March 2010 21:17
- Written by Guy Lambregts
- Hits: 3231
Pivot tables
Transaction at the Amsterdam Stock Exchange are stored in the table t_exchangerate_er.
In this example we limit the scope to transactions for the precious metals Gold, Silver, Platina and Palladium.
SQL> desc t_exchangerate_er;
Name Null? Type
----------------------------------------- -------- -----------------
ER_PK NOT NULL NUMBER
ER_FK_PM NOT NULL VARCHAR2(2)
ER_RATE NOT NULL NUMBER
ER_TIME NOT NULL TIMESTAMP(6)
With the select below we achieve the average transactionrate per month per precious metal
select
round(avg(er_rate),2) AVG_RATE,er_fk_pm,extract(month from er_time) MONTH,
extract(year from er_time) YEAR from t_exchangerate_er
group by
extract(year from er_time),
extract(month from er_time),
er_fk_pm
order by er_fk_pm,extract(year from er_time),extract(month from er_time);
AVG_RATE ER MONTH YEAR
---------- -- ---------- ----------
10.5 Ag 12 2008
14.67 Ag 6 2009
13 Ag 7 2009
15.5 Ag 3 2010
864.98 Au 12 2008
932.53 Au 6 2009
929.99 Au 7 2009
1060 Au 10 2009
1246.08 Au 2 2010
1115.01 Au 3 2010
182.54 PD 12 2008
245.97 PD 6 2009
245.03 PD 7 2009
440.14 PD 3 2010
1400 PT 12 2008
1400 PT 6 2009
1400 PT 7 2009
1400 PT 9 2009
1500.05 PT 3 2010
19 rows selected.
And now the rows to colums and the columns to rows dream. The SQL to reformat the output was rather complex in previous releases. Oracle 11G has introduced the PIVOT table clause.
select * from (select er_rate,er_fk_pm,extract(month from er_time) MONTH,extract(year from er_time) YEAR from t_exchangerate_er)
pivot
(avg(er_rate) for er_fk_pm in ('Ag','Au','PT','PD')) order by 1; MONTH YEAR 'Ag' 'Au' 'PT' 'PD' ---------- ---------- ---------- ---------- ---------- ---------- 2 2010 1246.08333 3 2010 15.5012165 1115.01385 1500.05058 440.14142 6 2009 14.6682538 932.532355 1400 245.969123 7 2009 12.9986916 929.994606 1400 245.029506 9 2009 1400 10 2009 1060 12 2008 10.500615 864.976157 1400 182.537516 7 rows selected.