MySQL – Select Rows with Similar ID and Merge Field Values

mergeMySQLrow

My situation is as follows. I have the query below and the query is doing its job pretty well. However I have excluded one able from it which is field_data_field_luogo and the rows inside this can have column entity_id and with different values in column field_luogo_tid , so what i want to do is , if entity_id is similar for 5 rows then merge the column of field_luogo_tid 's values in one column instead of having multiple rows.

SELECT a.nid,a.type,a.title,a.status,b.body_value,c.field_bici_value,
d.field_classifiche_url,d.field_classifiche_title,e.field_circuiti_tid,
f.field_corto_value,g.field_dcorto_value,h.field_medio_value,
i.field_dmedio_value,j.field_lungo_value,k.field_dlungo_value,
l.field_cronometraggio_tid,m.field_dalzeroit_value,n.field_data_value,
o.field_edizione_value,p.field_email_email,q.field_recapiti_value,
r.field_note_value,s.field_partenza_value,t.field_organizzatore_value,
u.field_star_value,v.field_superstar_value,w.field_tipomanifestazione_tid,
x.field_verifica_percorsi_value,tex.name as c_name , 
tex_t.name as cr_name,tex_n.name as m_name 
FROM node a 
LEFT JOIN field_data_body b ON b.entity_id = a.nid 
LEFT JOIN field_data_field_bici c ON c.entity_id = a.nid 
LEFT JOIN field_data_field_classifiche d ON d.entity_id = a.nid 
LEFT JOIN field_data_field_circuiti e ON e.entity_id = a.nid 
LEFT JOIN field_data_field_corto f ON f.entity_id = a.nid 
LEFT JOIN field_data_field_dcorto g ON g.entity_id = a.nid 
LEFT JOIN field_data_field_medio h ON h.entity_id = a.nid 
LEFT JOIN field_data_field_dmedio i ON i.entity_id = a.nid 
LEFT JOIN field_data_field_lungo j ON d.entity_id = a.nid 
LEFT JOIN field_data_field_dlungo k ON k.entity_id = a.nid 
LEFT JOIN field_data_field_cronometraggio l ON l.entity_id = a.nid 
LEFT JOIN field_data_field_dalzeroit m ON m.entity_id = a.nid 
LEFT JOIN field_data_field_data n ON n.entity_id = a.nid 
LEFT JOIN field_data_field_edizione o ON o.entity_id = a.nid 
LEFT JOIN field_data_field_email p ON p.entity_id = a.nid 
LEFT JOIN field_data_field_recapiti q ON q.entity_id = a.nid 
LEFT JOIN field_data_field_note r ON r.entity_id = a.nid 
LEFT JOIN field_data_field_partenza s ON s.entity_id = a.nid 
LEFT JOIN field_data_field_organizzatore t ON t.entity_id = a.nid 
LEFT JOIN field_data_field_star u ON u.entity_id = a.nid 
LEFT JOIN field_data_field_superstar v ON v.entity_id = a.nid 
LEFT JOIN field_data_field_tipomanifestazione w ON w.entity_id = a.nid 
LEFT JOIN field_data_field_verifica_percorsi x ON x.entity_id = a.nid 
LEFT JOIN taxonomy_term_data tex ON tex.tid = e.field_circuiti_tid 
LEFT JOIN taxonomy_term_data tex_t ON tex_t.tid = l.field_cronometraggio_tid LEFT JOIN taxonomy_term_data tex_n ON tex_n.tid = w.field_tipomanifestazione_tid 
WHERE a.type = 'manifestazione' 
AND a.status = 1  
GROUP BY a.nid

like if I have multiple rows with entity_id = 5 with field_luogo_id different in every row like :

2345
9877
9863
2374

by merging it will become :

2345,9877,9863,2374

Best Answer

You can use group concat for this. example

mysql> select  * from dummy;
+----+-------+
| id | name  |
+----+-------+
|  1 | fiest |
|  1 | fiest |
|  1 | sec   |
+----+-------+
3 rows in set (0.00 sec)

mysql> select group_concat(id) from dummy group by id;
+------------------+
| group_concat(id) |
+------------------+
| 1,1,1            |
+------------------+
1 row in set (0.00 sec)