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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!