[postgis-users] Crosstabbing a bunch of value columns (un-normalizing for GIS)

[John Abraham]

We have a bunch of tables with columns like this:

*GeometryUniqueID
*Year
*Scenario
Value

where * indicates the primary key

(And, of course, a separate table for the Geometry

*GeometryUniqueID
TheGeometry)

To compare values across scenarios with MapServer, QGIS, ArcGIS etc. we need it like this

*GeometryUniqueID
TheGeometry
Year1Scenario1Value
Year1Scenario2Value
Year1Scenario3Value

and to compare values across years we need it like this 

*GeometryUniqueID
TheGeometry
Year1Scenario1Value
Year2Scenario1Value
Year3Scenario1Value

That is, we want to compare values across scenarios and across years for the same geometry.  But we've normalized all our tables so that there is a row for each unique combination of scenario and year.  And, unfortunately, all of the GIS tools that will plot our data seem to expect the data to be in different columns in the same row (i.e., not normalized, at least not to my mind).

Is there a standard approach to appending each column to a view so that the PostGIS layer contains comparable values in different columns?

Right now we're thinking of a fairly complex select that joins the table to itself once for each year (or each scenario) but that doesn't feel right for many reasons, not the least of which we need to know the number of years (or scenarios) before we can write the view.  

Thanks in advance.  I don't want to reinvent the wheel here.  Crosstabbing in SQL is always hard but I bet someone on this list has solved this before.
--
John Abraham
jabraham at ucalgary.ca