Thread: PIVOT of data
Hi,
I have a table like this: location | numbrochures | marketing
-----------------------+--------------+-----------101 Asheville, NC | 4 | NEWS101 Asheville, NC |
1 | TV101 Asheville, NC | 3 | RADIO101 Asheville, NC | 2 | OTHER101 Asheville, NC
| 3 | null101 Asheville, NC | 1 | TV102 'Charlotte', 'NC' | 1 | SIGN104
'Colfax','NC' | 5 | SIGN109 '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/
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
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
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 totaltvFROM clientsGROUP BY client_address ) AS tv_totalsON tv_totals.client_address= clients.client_address LEFT OUTER JOIN (SELECT client_address, sum(news_ads) as totalnewsFROM clientsGROUP BY client_address ) AS news_totalsONnews_totals.client_address = clients.client_address LEFT OUTER JOIN (SELECT client_address, sum(posters) as totalpostersFROM clientsGROUP BY client_address ) AS poster_totalsONposter_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 informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Jamu, > I have been working with SQL databases for about a year in a half and > in that time I've come to the conclusion that it is not always > preferable to normalize to a high level (like 3rd level). In some > cases, depending on the application, I've found that normalizing to a > ridiculous degree has shot me in the foot in terms of the > responsibility of my code to manage what happens in the database. There I'd agree with you ... there is the question of *how rigorous* you want your normalization to be. I tend to hover around the simplest normal form, most of the time. And I do a few things (such as polymorhic sub-tables) that would give Fabian Pascal fits :-) However, there is (in my mind) no question as to whether a database should be normalized, just how much effort is spent on normalization as opposed to other considerations (UI, performance, development time). > I've found referential integrity makes normalization a less expensive > notion as it absolves you of the task of keeping data current in all > your tables. I've also found that working in an object oriented > language makes normalization less expensive. What do you use? I've given up on OODB interfaces, myself, as I did not find them helpful, but that's modtly because I was working in MS-land. > What are the views of the people on this list re: Normalization > Guidelines? Anybody know of any good web sites that talk about this > in depth? Yup. http://www.databasedebunking.com/ Dig through the archives. -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Hi Josh, > normal form, most of the time. And I do a few things (such as > polymorhic sub-tables) that would give Fabian Pascal fits :-) Polymorphic sub-tables? =) Do you mean using, for example, one user table that stores different types of users and has some fields specific to only some kinds of users and other fields that are shared by all users? > However, there is (in my mind) no question as to whether a database > should be normalized, just how much effort is spent on normalization as > opposed to other considerations (UI, performance, development time). I agree. Anyone here familiar with "Extreme Programming"?- they suggest that you should architect your software only for the functionality you need NOW. While some aspects of extreme progrmming have proven themselves true in the face of my skepticism I see it as my moral obligation as programmer to write programs that are A) as portable as possible and B) extensible. With that in mind, I like normalization because it increases the likelihood of scalability being relatively painless. > What do you use? I've given up on OODB interfaces, myself, as I did not > find them helpful, but that's modtly because I was working in MS-land. I've been working in Java with JDBC... I've actually shot myself in the foot a bit by not making my application object oriented enough. In hindsight, and if time permitted more refactoring, I would have written custom data objects to manage all interactions with the database so that any management of data happened in one and only one place and anything that needed to access those objects would do so through the common interface. > Yup. http://www.databasedebunking.com/ Dig through the archives. Couldn't find the server... I wonder if it's still there. Regards, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. jkakar@expressus.com 708-1641 Lonsdale Avenue V: (604) 903-6994 North Vancouver, BC, V7M 2J5
On Friday, 16. March 2001 00:28, jkakar@expressus.com wrote: > > > Yup. http://www.databasedebunking.com/ Dig through the archives. > > Couldn't find the server... I wonder if it's still there. Try this one: http://www.firstsql.com/dbdebunk/ Regards, Christof -- gl.aser . software engineering . internet service http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3
Jamu,
> Polymorphic sub-tables? =) Do you mean using, for example, one user
> table that stores different types of users and has some fields
> specific to only some kinds of users and other fields that are shared
> by all users?
Nope. What you described is a "not normalized" table. ;-)
One project contains a Notes table. However, I want it to be
configurable to what records notes apply ... that is, my application has
an admin option that allows you to configure the system so that there
are or aren't Notes for Client Addresses, for example.
TO do this, I created a sequence that is generally available
('universal_sq'), and set 7-9 tables to automatically increment a value
from the 'universal_sq' (column 'usq') for each record.
The Notes table, thus, effectively has the 'usq' as a foriegn key for 2
to 7 other tables, depending on user configuration. The wonderful
PostgreSQL sequence handler makes this possible. Thanks, Tom & team!
I've done this with a couple of other tables. Not relationally correct,
but I can't find anything wrong with the idea.
> Couldn't find the server... I wonder if it's still there.
I believe that Christof posted the correct URL.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco