Combine multiple rows into one space separated string

So I have 5 rows like this

userid, col
--------------
1, a
1, b
2, c
2, d
3, e

How would I do query so it will look like this

userid, combined
1, a b
2, c d
3, e

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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?

Method 2

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

Method 3

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

Method 4

1. MySQL with duplicates: select col1, group_concat(col2) from table1 group by col1
2. MySQL without duplicates: select col1, group_concat(distinct col2) from table1 group by col1
3. Hive with duplicates: select col1, collect_list(col2) from table1 group by col1
4. Hive without duplicates: select col1, collect_set(col2) from table1 group by col1

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0