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;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
