Oracle have bought in the much needed PIVOT feature in their 11g database to make our lives much easier, for those of us still living in the past it is still possible to "pivot" your data.

Take the following data for example recording (rare) bird sightings:

Id Bird Sighting
===================
1 Pigeon 01-JUN-10
2 Gull 01-JUN-10
3 Crow 02-JUN-10
4 Gull 02-JUN-10
5 Gull 02-JUN-10

To pivot this data you need to use DECODE. Decode allows to take a value, compare it and if it matches the value replace it with another value (check out PSOUG.org for a better example).

DECODE(input,compare,replace,else)

The following SQL will take the data and give you the bird names across the top and dates down the side:

SELECT Sighting,
SUM(DECODE(Bird,'Pigeon',1,0)) Pigeon,
SUM(DECODE(Bird,'Gull',1,0)) Gull,
SUM(DECODE(Bird,'Crow',1,0)) Crow
FROM Birds
GROUP BY Sighting

And you should get something like this:

Sighting Pigeon Gull Crow
==========================
01-JUN-10 1 1 0
02-JUN-10 0 2 1

This is fine however you need to hard code all the bird names, this is not very easy to maintain in the long run. In this example to way to get over this is to have the SQL query stored as a view, if you have a table containing the bird names you can then use a trigger to rewrite the view every time a bird is added to the table.

To run a create or replace view from within a PL/SQL trigger you can use the EXECUTE IMMEDIATE command.