From: Joel Burton <joel@joelburton.com>
To: Tille, Andreas <TilleA@rki.de>
To: rmello@fslc.usu.edu
Cc: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: Turning column into row
Date: Thu, 23 May 2002 08:56:23 -0400
Message-ID: <JGEPJNMCKODMDHGOBKDNOECDCPAA.joel@joelburton.com> (raw)
In-Reply-To: <Pine.LNX.4.44.0205231119570.8932-100000@wr-linux02.rki.ivbb.bund.de>
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Tille, Andreas
> Sent: Thursday, May 23, 2002 5:24 AM
> To: rmello@fslc.usu.edu
> Cc: PostgreSQL SQL
> Subject: Re: [SQL] Turning column into row
>
>
> On Wed, 22 May 2002, Joel Burton wrote:
>
> >
> http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?re
> cipe_id=13
> > 9 will do as a LIST() replacement.
> While beeing a great hint it has a syntactical mistake.
>
> The correct syntax would be:
>
> -- creat function to comma-ify a list
>
> create function comma_aggregate(text,text) returns text as '
> begin
> if (length($1) > 0 ) then
> return $1 || '', '' || $2; /* note the '' here !! */
> else
> return $2;
> end if;
> end;
> ' language 'plpgsql';
Yep. When you get the function back from \df+, it drops the doubling of
single-quotes.
> A further question: Is there any possibility to add a further flag in the
> way:
>
> select fname, lname, comma(hobbies,SPORT_FLAG) from people
> join hobbies on (personid) group by personid, fname, lname, SPORT_FLAG;
>
> So that we get only those hobbies listed which have SPORT_FLAG = 1
> or something else?
Not like that--aggregate functions can only take one argument. You could,
however, do something like:
SELECT fname, lname, comma ( SPORT_FLAG::char || hobby ) ..
and have the function examine the first character. If 0, don't add the thing
at all; if 1, drop the 1 and add as usual. I think that's way ugly and
hackish, but it would work.
A more SQL-ish way would be something like:
SELECT fname,
lname,
comma(hobby)
FROM people
JOIN hobbies
USING (personid)
WHERE sport_flag=1
GROUP BY personid, fname, lname;
But that would drop everyone that has hobbies but none that are sport_flag
hobbies.
If you wanted the people with hobbies but without sport_flag hobbies, you
could UNION them in at the end.
Alternatively, you could write the whole thing differently:
SELECT fname,
lname,
( SELECT comma(hobby)
FROM hobbies
WHERE sport_flag=1
AND h.personid=p1.personid
) AS hobbies
FROM people AS p0
This would show all people, regardless of whether they had any hobbies or
not.
In recent versions (7.2+, IIRC), this would probably be a good deal faster.
To see people w/o sports hobbies, you could change the join to an outer
join, and kick out people with count(hobbies)=0.
SELECT fname,
lname,
comma(hobby)
FROM people as P0,
JOIN ( SELECT * FROM hobbies WHERE sport_flag=1 ) AS h0
USING (personid)
GROUP BY personid, fname, lname;
HTH.
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: joel@joelburton.com, TilleA@rki.de, rmello@fslc.usu.edu
Subject: Re: Turning column into row
In-Reply-To: <JGEPJNMCKODMDHGOBKDNOECDCPAA.joel@joelburton.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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