Is there a function in Hive to rollup multiple records into one using a delimiter?

Question

I have a table with multiple lines for the same ID as below:

ID	Company
123456	CompanyA
123456	CompanyB
456123	CompanyB
789123	CompanyC
789123	CompanyD

Is there a function in Hive that can rollup multiple records into one using a delimiter like below:

ID	Company
123456	CompanyA, CompanyB
456123	CompanyB
789123	CompanyC, CompanyD

I tried using collect_list, but I am not getting the desired results. Hive does not have STUFF() like SQL.

TIA

Answer 1

Are you not able to get actual values from list generated by collect set? You can use below sql to overcome that.

Select 
Id,
concat_ws(',', collect_set(company)) as company
From mytable
Group by id

Now, if you want to order data in list, then sort the data in a subquery before collect list. Use sort by instead of order by.

Select 
Id,
concat_ws(',', collect_set(company)) as company
From 
(Select * from mytable sort by id, company) mytable
Group by id