Hi, I see how your idea could work--the only thing I would change would be region needs to be scanid on the first line; however, I'm using PostgreSQL v7.03, so I can not do subqueries within FROM clause.
Other than creating a temporary table, is there a way to restructure this solution to get around this limitation? thanks, jeremy Arian Prins <[EMAIL PROTECTED]> wrote in message news:<[EMAIL PROTECTED]>... > H Jeremy Bockholt schreef: > > > I have a generalized table: > > > > scanid | region | volume > > ------------------------- > > 1 A 34.4 > > 1 B 32.1 > > 1 C 29.1 > > 2 A 32.4 > > 2 B 33.2 > > 2 C 35.6 > > . > > . > > . > > > > I want to create a flattened out view that looks like the following: > > > > scanid | A_volume | B_volume | C_volume > > ---------------------------------------- > > 1 34.4 32.1 29.1 > > 2 32.4 33.2 35.6 > > . > > . > > . > > > > How do I correctly/efficiently construct a psql query to > > pivot/transpose the data? I am using postgreSQL version 7.0.x > > > > thanks, > > Jeremy > > Try This: > > select region, sum(a_volume) AS a_volume, > sum(b_volume) AS b_volume, > sum(c_volume) AS c_volume > from ( > select > scanid, > volume AS a_volume, > 0 AS b_volume, > 0 AS c_volume > from mytable > where region = A > UNION > select > scanid, > 0 AS a_volume, > volume AS b_volume, > 0 AS c_volume > from mytable > where region = B > UNION > select > scanid, > 0 AS a_volume, > 0 AS b_volume, > volume AS c_volume > from mytable > where region = C > ) tmp > > (you might have to specifically typecast the zero's) > > It would probably also be possible using CASE-statements. This is just > _one_ idea. > > Arian. ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
