MySQL provides an in built aggregate function called GROUP_CONCAT() to convert data in multiple rows into a single column.

SELECT location, GROUP_CONCAT(fist_name) FROM user
GROUP BY location;

Above query will return comma separated first_name of users grouped by their location.

Full syntax of GROUP_CONCAT function according to MySQL docs is below,

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

It offers DISTINCT as well as ORDER BY clauses within the function and the default separator of ‘,’ can be changed to anything using SEPARATOR clause followed by the required separator value, and empty quotes ’’ can be used for concatenating values without any separator.

NOTE: Concatenated value produced by GROUP_CONCAT function is defined by group_concat_max_len system variable and by default set to 1024 and any row data above the limit is discarded and a warning is shown for each truncated result. To change the value of group_concat_max_len variable at global or session level use,

SET [GLOBAL | SESSION] group_concat_max_len = value;

References:

1. MySQL dev documentation