Re: Concatenate rows

Michael Fuhr wrote:
> 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
> 

That works great, thank you very much.  I had been reading over the much 
documentation but mistakenly skipped arrays assuming it wouldn't be 
applicable.

A Gilmore