From: "Joel Burton" <joel@joelburton.com>
To: "Tille, Andreas" <TilleA@rki.de>, <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>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
In-Reply-To: 
 <Pine.LNX.4.44.0205231119570.8932-100000@wr-linux02.rki.ivbb.bund.de>
Importance: Normal

> -----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