Received: from mail.perdue.net (c696732-a.west1.ia.home.com [24.22.227.157]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2F3Rbx26750 for ; Wed, 14 Mar 2001 22:27:37 -0500 (EST) (envelope-from tperdue@mail.perdue.net) Received: (from tperdue@localhost) by mail.perdue.net (8.11.2/8.11.2/Debian 8.11.2-1) id f2F3QRc13699; Wed, 14 Mar 2001 21:26:27 -0600 Date: Wed, 14 Mar 2001 21:26:27 -0600 From: Tim Perdue To: Oliver Elphick Cc: Srikanth Rao , pgsql-sql@postgresql.org Subject: Re: PIVOT of data Message-ID: <20010314212627.H12986@mail.perdue.net> References: <200103142303.f2EN31G25419@linda.lfix.co.uk> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.3.12i In-Reply-To: <200103142303.f2EN31G25419@linda.lfix.co.uk>; from olly@lfix.co.uk on Wed, Mar 14, 2001 at 11:03:01PM +0000 X-Archive-Number: 200103/301 X-Sequence-Number: 1965 Probably worth noting that this could be normalized into at least 3 tables from what I can tell. Tim On Wed, Mar 14, 2001 at 11:03:01PM +0000, Oliver Elphick wrote: > Srikanth Rao wrote: > >Hi, > >I have a table like this: > > location | numbrochures | marketing > >-----------------------+--------------+----------- > > 101 Asheville, NC | 4 | NEWS > > 101 Asheville, NC | 1 | TV > > 101 Asheville, NC | 3 | RADIO > > 101 Asheville, NC | 2 | OTHER > > 101 Asheville, NC | 3 | null > > 101 Asheville, NC | 1 | TV > > 102 'Charlotte', 'NC' | 1 | SIGN > > 104 'Colfax', 'NC' | 5 | SIGN > > 109 'Moyock', 'NC' | 1 | BROCHURE > >(9 rows) > > > > > >I want the headings to be like: > > > >location | NEWS | TV | RADIO | OTHER | ........ > > > >How to get this done using sql for postgresql backend? > > SELECT location, > CASE WHEN marketing = 'NEWS' > THEN numbrochures > ELSE NULL > END AS "NEWS", > CASE WHEN marketing = 'TV' > THEN numbrochures > ELSE NULL > END AS "TV", > ... > > but it's a clumsy hack and won't work if you don't know the > contents of "marketing" in advance. > > -- > Oliver Elphick Oliver.Elphick@lfix.co.uk > Isle of Wight http://www.lfix.co.uk/oliver > PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "Let your light so shine before men, that they may see > your good works, and glorify your Father which is in > heaven." Matthew 5:16 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Founder - PHPBuilder.com / Geocrawler.com Lead Developer - SourceForge VA Linux Systems