Received: from coopers.dsl.net (coopers.dsl.net [65.84.81.5]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9VJrGP04269 for ; Wed, 31 Oct 2001 14:53:16 -0500 (EST) (envelope-from james@lrgmail.com) Received: from jameslt (65-85-235-195.client.dsl.net [65.85.235.195]) by coopers.dsl.net (Postfix) with SMTP id B67FE1FD36; Wed, 31 Oct 2001 14:37:15 -0500 (EST) Message-ID: <000e01c16246$7f1b1a80$1600000a@lrg.office> From: "James Orr" To: "H Jeremy Bockholt" , References: Subject: Re: transposing data for a view Date: Wed, 31 Oct 2001 14:59:02 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200110/429 X-Sequence-Number: 5380 ----- Original Message ----- From: "H Jeremy Bockholt" To: Sent: Tuesday, October 30, 2001 7:36 PM Subject: [SQL] transposing data for a view > 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 SELECT A.scanid, A.volume AS A_volume, B.volume AS B_volume, C.volume AS C_volume FROM table A JOIN table B ON (A.scanid = B.scanid) JOIN table C ON (B.scanid = C.scanid) WHERE A.region = 'A' AND B.region = 'B' AND C.region = 'C' - James