Re: Turning column into row

> -----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=1GROUPBY 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