How do I concatenate fragmented messages of strings that are out of order in SQL

Question

I have a table with three columns the first column indicates a message ID (message_id) the second column represents an ordinal feature which indicates the order of the message (message_order), lastly the third column is a fragment of the message(message_fragment):

+------------+---------------+------------------------------+
| message_id | message_order |           message            |
+------------+---------------+------------------------------+
| Message 1  |             2 | Best, Jose                   |
| Message 1  |             1 | Thanks for your advice       |
| Message 2  |             1 | I only have one line of text |
+------------+---------------+------------------------------+

Is there a way in SQL to concatenate the message rows by message in order of message order? To ultimately get the following:

+------------+-----------------------------------+
| message_id |              message              |
+------------+-----------------------------------+
| Message 1  | Thanks for your advice Best, Jose |
| Message 2  | I only have one line of text      |
+------------+-----------------------------------+

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Nov 08 '19 at 15:28
Which dbms are you using? (The solution will probably use product specific functionality.) — jarlh, Nov 08 '19 at 15:28

score 0 · Answer 1 · answered Nov 08 '19 at 15:53

0

For Oracle:

with msg(msg_id, msg_order, msg_text) as (
  select 1, 2, 'Best, Jose' from dual union all
  select 1, 1, 'Thanks for your advice' from dual union all
  select 2, 1, 'I only have one line of text' from dual
)
select msg_id, listagg(msg_text, ' ') within group (order by msg_id, msg_order) message
from msg
group by msg_id;

answered Nov 08 '19 at 15:53

Gary_W

9,933
1
22
40

hive doesn't have `listagg` function – Vamsi Prabhala Nov 08 '19 at 16:35
Alas, at the time I posted you did not state what RDBMS you were using. I'll leave this here for future searchers, Try searching for "hive equivalent of oracle listagg". – Gary_W Nov 08 '19 at 16:37

leftjoin · Answer 2 · 2019-11-08T18:32:21.400

0

For Hive:

select message_id, concat_ws(' ', collect_list(message)) as message
  from
      (select message_id, message_order, message 
         from table
       distribute by message_id sort by message_order 
      )s  
 group by message_id
 order by message_id;

edited Nov 08 '19 at 18:32

answered Nov 08 '19 at 17:55

leftjoin

36,950
8
57
116

How do I concatenate fragmented messages of strings that are out of order in SQL

2 Answers2

Linked