CODEWITHSUNDEEP
postgresqlmergemultiple-columns
Sandeep Sukhija
Sandeep Sukhija

Reputation: 1176

postgresql: SQL Query to merge the contents from multiple rows using another column

I have the table history with following columns:

row_id, msgid, sender, receiver, chatcontent, transdate, transtime

Each chat transcript is stored as a separate row in the table.

Scenario: My framework splits the chat content in multiple transcripts if the content is more then 2048 characters, The data is stored in mulitple rows with same details such as msgid, sender, receiver, transdate and transtime only chatcontent is different with row_id as the sequence.

For example, my contents in table are

001, msgid1, [email protected], [email protected], this is a long tes, 2013-03-13, 13:55:34
002, msgid1, [email protected], [email protected], t message which is, 2013-03-13, 13:55:34
003, msgid1, [email protected], [email protected],  splitted in multi, 2013-03-13, 13:55:34
004, msgid1, [email protected], [email protected], ple rows, 2013-03-13, 13:55:34
005, msgid2, [email protected], [email protected], yup i got you, 2013-03-13, 13:56:12

Now I want to fetch the data in a single query which should have output as

msgid1, [email protected], [email protected], this is a long test message which is splitted in multiple rows, 2013-03-13, 13:55:34
msgid2, [email protected], [email protected], yup i got you, 2013-03-13, 13:56:12

How to do it. I am unable to fetch all the details in the single query. I am able to merge the chatcontent in one column using command but i dont want to hardcode the value of msgid

SELECT array_to_string(array(SELECT chatcontent FROM history where msgid='msgid1'),'');

Upvotes: 3

Views: 9615

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125534

SELECT
    msgid,
    sender,
    receiver,
    transdate,
    transtime,
    array_to_string(array(
        select chatcontent
        from history
        where msgid = h.msgid
        order by row_id
        ), ' '
    ) chatcontent
from history h
group by 1, 2, 3, 4, 5, 6
order by 1

Upvotes: 9

Related Questions