Re: Concatenate rows

On Sun, Feb 20, 2005 at 05:32:48PM -0800, A Gilmore wrote:

> If I have a table like :
>
> ID    COLOR
> 1    blue
> 2    red
> 3    green
>
> Could I write a SQL query that would return a single concatenated row
> for all matching rows, with a specified seperator. For example like so :
>
> blue:red:green:

In PostgreSQL 7.4 and later you could do this:

SELECT array_to_string(array(SELECT color FROM foo), ':');

See "Array Constructors", "Arrays", and "Array Functions and
Operators" in the documentation.  Here are links for the latest
version of PostgreSQL:

http://www.postgresql.org/docs/8.0/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
http://www.postgresql.org/docs/8.0/static/arrays.html
http://www.postgresql.org/docs/8.0/static/functions-array.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/