6

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.

4
  • group_concat() is your friend. Commented Aug 3, 2016 at 21:03
  • 1
    Could be. Maybe string_agg() , then? Commented Aug 3, 2016 at 21:09
  • Quick question though. How can I make a line break for each new message? Commented Aug 3, 2016 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. Commented Aug 3, 2016 at 21:14

1 Answer 1

6

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.

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.