PostgreSQL: How to concatenate rows

Question

So, I have a pretty unconventional problem. I would like to be able to concatenate rows with the same ID into one big row. To illustrate my problem let me provide an example. Here is the query:

SELECT b.id AS "ID",
       m.content AS "Conversation"
FROM bookings b 
INNER JOIN conversations c on b.id = c.conversable_id AND c.conversable_type = 'Booking'
INNER JOIN messages m on m.conversation_id = c.id
WHERE b.state IS NOT NULL
GROUP BY 1,2
LIMIT 1000;

And here is the output:

ID     **Conversation
1223    "blah, blah, blah, blah"
1223    " ... blaaaah, blah.."
1223    "more blaaah"
1223    "last blah"
5000    "new id, with more blah"
5000    "and the blah continues"

Is there a way to concatenate the conversation rows into one aggregate row while keeping the ID?

Like this:

ID     Conversation
1223    "blah, blah, blah, blah, ... blaaaah blah.. more blaaah, last blah"
5000    "new id, with more blah and the blah continues"

I am sure there is an efficient way to do this. I just can't figure it out on my own.

Quick question though. How can I make a line break for each new message? — DBE7, Aug 03 '16 at 21:12
There are no linebreaks. You could use `string_agg( colx, e'\n' )` , but it will still be part of the output record.field. — wildplasser, Aug 03 '16 at 21:14

score 5 · Answer 1 · edited May 23 '17 at 11:51

5

I was able to solve my own problem by looking at the brilliant answers to this question. It was as simple as using the PostgreSQL string_agg()-function.

edited May 23 '17 at 11:51

Community

1
1

answered Aug 03 '16 at 21:10

DBE7

766
2
9
23

PostgreSQL: How to concatenate rows

1 Answers1