Pivot table

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(n²). 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