Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fA1DbFP07793 for ; Thu, 1 Nov 2001 08:37:15 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fA1DFD804276 for pgsql-sql@postgresql.org; Thu, 1 Nov 2001 08:15:13 -0500 (EST) (envelope-from news) From: Arian Prins X-Newsgroups: comp.databases.postgresql.sql Subject: Re: transposing data for a view Date: Wed, 31 Oct 2001 08:51:36 +0100 Organization: Academic Medical Center, The Netherlands Lines: 72 Message-ID: <3BDFAD88.6BB058DA@zonnet.nl> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.surfnet.nl To: H Jeremy Bockholt X-Mailer: Mozilla 4.7 [nl] (WinNT; I) X-Accept-Language: nl Cache-Post-Path: sphere.amc.uva.nl!unknown@amcnip36143.amc.uva.nl X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/) To: pgsql-sql@postgresql.org X-Archive-Number: 200111/7 X-Sequence-Number: 5393 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.