Re: pg SQL question

On Sat, Jan 22, 2005 at 02:03:58PM -0700, Ed L. wrote:
>
> There's probably an obvious answer for this, but I couldn't see it
> in the docs.  What's the simplest way to concatenate multiple
> same-column values in SQL?
>
> For example, suppose I have table foo (key integer, id integer,
> entry varchar) with data
>
>     key    id    entry
>     1    1    "Four score and seven years ago our fathers "
>     1    2    "brought forth on this continent, a new nation, "
>     1    3    "conceived in Liberty, and dedicated to the "
>     1    4    "proposition that all men are created equal."
>
> and I want to produce the following result:
>
> "Four score and seven years ago our fathers brought forth on this
> continent, a new nation, conceived in Liberty, and dedicated to the
> proposition that all men are created equal."

SELECT f.key, array_to_string(ARRAY(
    SELECT entry
    FROM  foo
    ORDER BY id
    WHERE key = f.key
), '') AS "blurb"
FROM foo f;

> I know this could be done writing a plpgsql function, but it seems
> so basic, I thought there might be something I'm overlooking.

Well, it's not *totally* basic, and it draws on a few different
things, but you can do it with builtins.

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!