I want to convert selected values into a comma separated string in MySQL. My initial code is as follows:
SELECT id FROM table_level where parent_id=4;
Which produced:
'5'
'6'
'9'
'10'
'12'
'14'
'15'
'17'
'18'
'779'
My desired output would look like this:
"5,6,9,10,12,14,15,17,18,779"
Use group_concat
method in mysql
If you have multiple rows for parent_id.
SELECT GROUP_CONCAT(id) FROM table_level where parent_id=4 GROUP BY parent_id;
If you want to replace space with comma.
SELECT REPLACE(id,' ',',') FROM table_level where parent_id=4;
First to set group_concat_max_len
, otherwise it will not give you all the result:
SET GLOBAL group_concat_max_len = 999999;
SELECT GROUP_CONCAT(id) FROM table_level where parent_id=4 group by parent_id;
The default separator between values in a group is comma(,). To specify any other separator, use SEPARATOR
as shown below.
SELECT GROUP_CONCAT(id SEPARATOR '|')
FROM `table_level`
WHERE `parent_id`=4
GROUP BY `parent_id`;
5|6|9|10|12|14|15|17|18|779
To eliminate the separator, then use SEPARATOR ''
SELECT GROUP_CONCAT(id SEPARATOR '')
FROM `table_level`
WHERE `parent_id`=4
GROUP BY `parent_id`;
Refer for more info GROUP_CONCAT
Use group_concat() function of mysql.
SELECT GROUP_CONCAT(id) FROM table_level where parent_id=4 GROUP BY parent_id;
It'll give you concatenated string like :
5,6,9,10,12,14,15,17,18,779
Just so for people doing it in SQL server: use STRING_AGG
to get similar results.
Try this
SELECT CONCAT('"',GROUP_CONCAT(id),'"') FROM table_level
where parent_id=4 group by parent_id;
Result will be
"5,6,9,10,12,14,15,17,18,779"
Source: Stackoverflow.com