Re: PIVOT of data

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) > >
>Iwant the headings to be  like: > >location | NEWS | TV | RADIO | OTHER | ........ > >How to get this done using sql
forpostgresql 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,
andglorify your Father which is in      heaven."         Matthew 5:16