Often I need to extract a list of articles IDs from my MySql databases but getting the result in the default column is not enough and it can be easier to convert the result from a column into a one line list of comma separated results, for instance from
Select id from myTable
ID |
---|
64 |
85 |
25 |
63 |
but your expected output is 64, 85, 25, 63
To concatenate multiple rows results into a single mySql field you can use the group_concat built-in function (to make sure your MySql version has this function you would like to check in the manual).
Let’s see a real life example query where we can apply this function. In my db I have a list of additional information items identified by ID related to a specific website entry/profile. The query below allows me to collect all the IDs of the entries that relate to ‘www.example.com’
SELECT GROUP_CONCAT(additional_information_id) FROM `company_profile_additionalinformation` WHERE profile_website ='www.example.com' ;
Please note the result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. This can be changed. Check the manual for more information .