15

I need to concat string values row wise with '~' as delimiter. I have the following data:

enter image description here

I need to concat 'Comment' column for each 'id' in the ascending order of 'row_id' with '~' as delimiter.

Expected output is as below:

enter image description here

GROUP_CONCAT is not an option since its not recognized in my Hive version. I can use collect_set or collect_list, but I won't be able to insert delimiter in between.

Is there any workaround?

Kate Orlova
  • 3,225
  • 5
  • 11
  • 35
Vaishak
  • 607
  • 3
  • 8
  • 30

1 Answers1

39

collect_list returns array, not string.
Array can be converted to delimited string using concat_ws.


This will work, with no specific order of comments.

select      id
           ,concat_ws('~',collect_list(comment)) as comments

from        mytable 

group by    id
;

+----+-------------+
| id |  comments   |
+----+-------------+
|  1 | ABC~PRQ~XYZ |
|  2 | LMN~OPQ     |
+----+-------------+
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88