To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: How to collect text-fields from multiple rows ?
Date: Sat, 16 Oct 2010 09:23:03 +0200
Lines: 38
Message-ID: <i9bjsn$l4b$1@dough.gmane.org>
References: <4CB91AAE.6030602@gmx.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To: <4CB91AAE.6030602@gmx.net>

Andreas wrote on 16.10.2010 05:23:
> Hi,
>
> how can I collect text-fields from multiple rows into one output row?
> I'd like to do an equivalent to the aggregate function SUM() only for text.
>
> The input is a select that shows among other things a numerical column where I would like to group by.
> The text column of all rows in a group should get concatenated into 1 text devided by a '\n'.
>
> Even better would be if I could add a second text colum per line as topic.
>
> Input e.g.
> select group_nr::integer, memo::text, topic::text ...
>
> 1, 'bla ', 'weather'
> 2, 'yada..', 'weather'
> 2, 'talk talk..', 'cooking'
> 2, 'words words, ...', 'poetry'
> 3, ....
>
> Output:
>
> 1, 'weather\nbla...'
> 2, 'weather\nyada..\ncooking\ntalk talk..\npoetry\nwords words, ...'
>

If you are on 9.0:

SELECT group_nr, string_agg(memo||'--'||topic, '--')
FROM the_table_with_no_name
GROUP BY group_nr;

On 8.x you need to user array_agg()

SELECT group_nr, array_to_string(array_agg(memo||'--'||topic),'--')
FROM the_table_with_no_name
GROUP BY group_nr;