Re: PIVOT of data - Mailing list pgsql-sql
From | Tim Perdue |
---|---|
Subject | Re: PIVOT of data |
Date | |
Msg-id | 20010314212627.H12986@mail.perdue.net Whole thread Raw |
In response to | Re: PIVOT of data ("Oliver Elphick" <olly@lfix.co.uk>) |
Responses |
Re: PIVOT of data
("Josh Berkus" <josh@agliodbs.com>)
|
List | pgsql-sql |
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