hive: Using collect_set with a delimiter

Question

I am trying to use collect_set in a hive query to group the columns on a group by condition. Each row is having spaces as delimiters instead of ',' or something. How to change the delimiter to ',' or any other delimiter in this case?

Thanks in advance.

Regards, Naga Vemprala

Answer 1

COLLECT_SET returns an array, with which you can then concatenate the entries into a single comma-separated value using CONCAT_WS:

select serial_num, concat_ws(",", collect_set(customer_lastName)) as concatenate_lastNames from External_Table group by serial_num