[thelist] Postgres / General SQL question...

[Matt Warden]

On Wed, Feb 4, 2009 at 8:13 AM, Tris <beertastic at gmail.com> wrote:
> Hi all...
> I'm trying to retrieve a result set using only one sql statement.
>
> I've a 2 tables I'm joining to: users, locations
>
> a user can exist in multiple (though we limit it to 5) locations, and
> I've been asked to produce a report that has 6 columns
> User Id
> Location 1
> Location 2
> ...
> Location 5
>
> I can export all users with each location on it's own row.. but I
> don't know how to tell postgres to treat each instance of a location
> and apply it to the next column, rather than the next row.

This is a "crosstab query". Our very own Rudy has written a number of
'ask the expert' columns on it:

http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_cid556537,00.html
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_cid558168,00.html
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_cid911919,00.html
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1314430,00.html

Others: http://www.google.com/search?q=crosstab+query


-- 
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.