Combine multiple rows into one space separated string

In hive you can use

SELECT userid, collect_set(combined) FROM tabel GROUP BY user_id;

collect_set removes duplicated. If you need to keep them you can check this post:

COLLECT_SET() in Hive, keep duplicates?

Use the GROUP_CONCAT aggregate function:

  SELECT yt.userid,
         GROUP_CONCAT(yt.col SEPARATOR ' ') AS combined
    FROM YOUR_TABLE yt
GROUP BY yt.userid

The default separator is a comma (","), so you need to specify the SEPARATOR of a single space to get the output you desire.

If you want to ensure the order of the values in the GROUP_CONCAT, use:

  SELECT yt.userid,
         GROUP_CONCAT(yt.col ORDER BY yt.col SEPARATOR ' ') AS combined
    FROM YOUR_TABLE yt
GROUP BY yt.userid

SELECT 
  userid,
  concat_ws(" ", collect_set(col)) AS combined
FROM table 
GROUP BY userid

Related