Thread: Concatenate rows

Hello,

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:

Thank you for your time,
A Gilmore

I suppose you could create a function, use a cursor to walk through the table row by row, assign the result of the each
rowfetched to the same variable each time. 

CREATE FUNCTION returns text
DECLARE
a text;
single_row text;

BEGIN

FETCH CURSOR INTO

 a := SELECT COLOR FROM X;

 single_row := single_row || ':' || a;

LOOP;

END;

return single_row;


On Sun, Feb 20, 2005 at 05:32:48PM -0800, A Gilmore wrote:
> Hello,
>
> 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:
>
> Thank you for your time,
> A Gilmore
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org

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/

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