Objective is to make table unique at ID. Table structure is as in attached image.

Query used is : 

select

ID,

concat_ws(' & ' , collect_list(Distinct Gender)) as Gender

from table

group by ID

It can be possible if we can order values within collect_list and then concat but I can't figure out how to do that.

@Hubert Dudek​ @Werner Stinckens​ @Aviral Bhardwaj​ @Hemanth A​ @Omkar G​ @Yogita Chavan​ @Gam Nguyen​ @Aman Sehgal​ @Ajay Pandey​ @pat​ @Hau Nguyen​