Idnan Idnan - 3 months ago 20
MySQL Question

Mysql - group and concat multiple columns

I have data like this:

| hotel_id | city_name_en | country_name_en | city_name_ar | country_name_ar |
|----------|--------------|----------------------|--------------|-----------------|
| 4020874 | Dubai | United Arab Emirates | XXXX | XXXXXXXX |
| 4020882 | Dubai | United Arab Emirates | XXXX | XXXXXXXX |
| 4020890 | Dubai | United Arab Emirates | XXXX | XXXXXXXX |
| 4000053 | Al Fujayrah | United Arab Emirates | YYYY | YYYYYYYY |
| 4000058 | Al Fujayrah | United Arab Emirates | YYYY | YYYYYYYY |
| 4000060 | Al Fujayrah | United Arab Emirates | YYYY | YYYYYYYY |


and i want an output like this:

| hotel_id_list | name_en | name_ar |
|---------------------------|-----------------------------------|--------------------|
| 4020874, 4020882, 4020890 | Dubai, United Arab Emirates | XXXX, XXXXXXXXXXXX |
| 4000053, 4000058, 4000060 | Al Fujayrah, United Arab Emirates | YYYY, YYYYYYYYYYYY |


I want to concat hotel ids and group them by city_name_en and city_name_ar.

This is my query that i tried so far.

select
GROUP_CONCAT(DISTINCT hotel_id) AS 'hotel_id_list'
from (
select g1.hotel_id, g1.country_code, g1.city_name as city_name_en, g1.country_name as country_name_en, g2.city_name as city_name_ar, g2.country_name as country_name_ar
from general_details g1
inner join general_details g2 on g1.hotel_id = g2.hotel_id and g1.language_id=0 and g2.language_id=1) as t
where country_code = 'AE'


I can concat hotel ids but i also want to concat city and country.

Answer

Something like this should work:

SELECT GROUP_CONCAT(`hotel_id`) AS `hotel_id_list`
CONCAT(`city_name_en`, ', ', `country_name_en`) AS `name_en`
CONCAT(`city_name_ar`, ', ', `country_name_ar`) AS `name_ar`
FROM `<yourtablename>`
GROUP BY `city_name_en`, `country_name_en`, `city_name_ar`, `country_name_ar`
Comments