Skip site navigation (1) Skip section navigation (2)

Re: Concatenate rows

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: A Gilmore <agilmore(at)shaw(dot)ca>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Concatenate rows
Date: 2005-02-21 04:22:36
Message-ID: 20050221042236.GA28383@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
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/

In response to

Responses

pgsql-novice by date

Next:From: IainDate: 2005-02-21 06:42:23
Subject: Re: PostgreSQL Errors...
Previous:From: Mike G.Date: 2005-02-21 03:34:27
Subject: Re: Concatenate rows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group