One approach is to use joins from the same att_table onto your geom_table using aliases:
SELECT geom_table.GeometryUniqueID, geom_table.TheGeometry, Year1Scenario1.Value AS Year1Scenario1Value, Year1Scenario2.Value AS Year1Scenario2Value, Year1Scenario3.Value AS Year1Scenario3Value FROM geom_table LEFT JOIN att_table Year1Scenario1 ON Year1Scenario1.GeometryUniqueID = geom_table.GeometryUniqueID AND Year1Scenario1.Scenario = 1 AND Year1Scenario1.Year = 1 LEFT JOIN att_table Year1Scenario2 ON Year1Scenario2.GeometryUniqueID = geom_table.GeometryUniqueID AND Year1Scenario2.Scenario = 2 AND Year1Scenario2.Year = 1 LEFT JOIN att_table Year1Scenario3 ON Year1Scenario3.GeometryUniqueID = geom_table.GeometryUniqueID AND Year1Scenario2.Scenario = 3 AND Year1Scenario3.Year = 1 http://www.postgresql.org/docs/current/static/tutorial-join.html <http://www.postgresql.org/docs/current/static/tutorial-join.html>-Mike On 19 April 2010 10:24, John Abraham <[email protected]> wrote: > 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 > [email protected] > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
