Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fA1DbGP07795 for ; Thu, 1 Nov 2001 08:37:16 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fA1DFLL04286 for pgsql-sql@postgresql.org; Thu, 1 Nov 2001 08:15:21 -0500 (EST) (envelope-from news) From: jeremy@wundt.psychiatry.uiowa.edu (H Jeremy Bockholt) X-Newsgroups: comp.databases.postgresql.sql Subject: Re: transposing data for a view Date: 31 Oct 2001 10:27:47 -0800 Organization: http://groups.google.com/ Lines: 81 Message-ID: References: <3BDFAD88.6BB058DA@zonnet.nl> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-abuse@google.com To: pgsql-sql@postgresql.org X-Archive-Number: 200111/6 X-Sequence-Number: 5392 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 wrote in message news:<3BDFAD88.6BB058DA@zonnet.nl>... > 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.