pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to collect text-fields from multiple rows ?
2+ messages / 2 participants
[nested] [flat]
* How to collect text-fields from multiple rows ?
@ 2010-10-16 03:23 Andreas <maps.on@gmx.net>
2010-10-16 07:23 ` Re: How to collect text-fields from multiple rows ? Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 2+ messages in thread
From: Andreas @ 2010-10-16 03:23 UTC (permalink / raw)
To: pgsql-sql
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, ...'
Even better would be to get some kind of line-chars as optical devider
between the topics.
Lets say 10 = above all topic-lines.
2, '==========\nweather\nyada..\n==========\ncooking\ntalk
talk..\n==========\npoetry\nwords words, ...'
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How to collect text-fields from multiple rows ?
2010-10-16 03:23 How to collect text-fields from multiple rows ? Andreas <maps.on@gmx.net>
@ 2010-10-16 07:23 ` Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 0 replies; 2+ messages in thread
From: Thomas Kellerer @ 2010-10-16 07:23 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-10-16 07:23 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-16 03:23 How to collect text-fields from multiple rows ? Andreas <maps.on@gmx.net>
2010-10-16 07:23 ` Thomas Kellerer <spam_eater@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox