pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPIVOT of data
4+ messages / 4 participants
[nested] [flat]
* PIVOT of data
@ 2001-03-14 22:29 Srikanth Rao <srirao_us@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Srikanth Rao @ 2001-03-14 22:29 UTC (permalink / raw)
To: pgsql-sql
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?
TIA,
Sri.
__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - Buy the things you want at great prices.
http://auctions.yahoo.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PIVOT of data
@ 2001-03-14 23:03 Oliver Elphick <olly@lfix.co.uk>
parent: Srikanth Rao <srirao_us@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Oliver Elphick @ 2001-03-14 23:03 UTC (permalink / raw)
To: Srikanth Rao <srirao_us@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PIVOT of data
@ 2001-03-15 03:26 Tim Perdue <tim@perdue.net>
parent: Oliver Elphick <olly@lfix.co.uk>
0 siblings, 1 reply; 4+ messages in thread
From: Tim Perdue @ 2001-03-15 03:26 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Srikanth Rao <srirao_us@yahoo.com>; 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PIVOT of data
@ 2001-03-15 05:04 Josh Berkus <josh@agliodbs.com>
parent: Tim Perdue <tim@perdue.net>
0 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2001-03-15 05:04 UTC (permalink / raw)
To: pgsql-sql
Srikanth,
Aside from seconding Tim (your database is *not* normalized), the PIVOT
function is only available one of 2 ways:
SQL-ONLY, FIXED COLUMN: If you know your categories in advance, you can
write a complex view using outer joins, sub-selects, and aggregates.
Imagine that we know your types of marketing to be limited to TV, NEWS
and POSTER:
SELECT client_address, tv_totals.totaltv, news_totals.totalnews,
poster_totals.totalposter
FROM clients
LEFT OUTER JOIN (
SELECT client_address, sum(tv_spots) as totaltv
FROM clients
GROUP BY client_address ) AS tv_totals
ON tv_totals.client_address = clients.client_address
LEFT OUTER JOIN (
SELECT client_address, sum(news_ads) as totalnews
FROM clients
GROUP BY client_address ) AS news_totals
ON news_totals.client_address = clients.client_address
LEFT OUTER JOIN (
SELECT client_address, sum(posters) as totalposters
FROM clients
GROUP BY client_address ) AS poster_totals
ON poster_totals.client_address = clients.client_address;
DYNAMIC METHOD: This requires an external programming language, and I'm
not going to write it for you! However, pick you favorite procedural
language (tcl, perl, or C) and write the following:
1. Do an select distinct to obtain each value in the column you want to
use in the headers. Load this series into an array.
2. Dynamically generate query text, adding a column and a LEFT OUTER
JOIN as above into the query for each value in your array.
This method is a lot of work for both you and the server. Why do you
think that MS Access often crashes on a large "crosstab" query?
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-03-15 05:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-14 22:29 PIVOT of data Srikanth Rao <srirao_us@yahoo.com>
2001-03-14 23:03 ` Oliver Elphick <olly@lfix.co.uk>
2001-03-15 03:26 ` Tim Perdue <tim@perdue.net>
2001-03-15 05:04 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox