Re: Turning column into row

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Tille, Andreas
> Sent: Thursday, May 23, 2002 5:24 AM
> To: rmello(at)fslc(dot)usu(dot)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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant