A pivot table is a record set generated from a
SQL statement that converts rows to columns and vice versa. This is most useful for performing "top n" queries, or for creating a
view that makes other queries easier. Consider a table of athletic results (called
results):
EVENT POSITION ATHLETE
---------- ----------- ----------
Discus 1 Bob
Discus 2 John
Discus 3 Bill
Discus 4 Al
Shot Putt 1 Mary
Shot Putt 2 Helen
Shot Putt 3 Linda
Shot Putt 4 Sara
To generate a report of gold, silver and bronze medal winners:
create view medals as select event, max(decode(position, 1, athlete, null)) as gold, max(decode(position,2, athlete, null)) as silver, max(decode(position,3, athlete, null)) as bronze from results group by event
Now, the underlying table can be queried from medals (select * from medals):
EVENT GOLD SILVER BRONZE
---------- ---------- ---------- ----------
Discus Bob John Bill
Shot Putt Mary Helen Linda
This technique is specific to Oracle, the Sybase equivalent requires the use of a self join and hence does not scale as well when there are many columns in the view - a table scan with decode and group by is O(n log n) but a self join is at least O(n2). However, since it does not rely on the decode function, it is SQL92 compliant.
create view medals as select r1.event, r1.athlete "gold", r2.athlete "silver", r3.athlete "bronze"
from results r1, results r2, results r3
where r1.event = r2.event and r2.event = r3.event
and r1.position = 1 and r2.position = 2 and r3.position = 3
And the medals can be queried as before with select * from medals
EVENT gold silver bronze
---------- ---------- ---------- ----------
Discus Bob John Bill
Shot Putt Mary Helen Linda