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 produces:
'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"
Henry Ecker
32.6k17 gold badges30 silver badges50 bronze badges
asked Oct 24, 2013 at 6:22
Check this:
SELECT GROUP_CONCAT[id]
FROM table_level
WHERE parent_id = 4
GROUP BY parent_id;
informatik01
15.8k10 gold badges73 silver badges102 bronze badges
answered Oct 24, 2013 at 6:28
naveen goyalnaveen goyal
4,4412 gold badges15 silver badges26 bronze badges
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;
answered Oct 24, 2013 at 6:29
Sanal KSanal K
7234 silver badges14 bronze badges
1
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
answered Oct 24, 2013 at 6:33
Nishu TayalNishu Tayal
19.5k8 gold badges47 silver badges97 bronze badges
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"
answered Oct 24, 2013 at 6:30
Ankit SharmaAnkit Sharma
3,8162 gold badges28 silver badges46 bronze badges
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;
wscourge
9,60912 gold badges53 silver badges73 bronze badges
answered Jan 16, 2018 at 6:30
cksahucksahu
1311 silver badge4 bronze badges
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
answered Jan 27, 2017 at 19:27
Rohan KhudeRohan Khude
4,1665 gold badges47 silver badges42 bronze badges
1
Use
group_concat method in mysql
kamal pal
4,1495 gold badges24 silver badges39 bronze badges
answered Oct 24, 2013 at 6:27
hepizojhepizoj
2434 silver badges9 bronze badges
2
Just so for people doing it in SQL server: use STRING_AGG to get similar results.
answered May 28, 2020 at 1:45
DPPDPP
12.3k2 gold badges46 silver badges46 bronze badges
Using the GROUP_CONCAT, here is another way to make it flexible :
SELECT GROUP_CONCAT['"',id,'"'] FROM table_level where parent_id=4 GROUP BY parent_id;
This will return the values as :
"181","187","193","199","205","211","217","223","229","235","239","243","247","251"
You can concat using any other separator. This will help in case you want to use the return value directly somewhere.
answered Aug 12, 2021 at 15:51
SELECT GROUP_CONCAT[id] as ids FROM table_level where parent_id=4 group by parent_id;
answered Nov 18, 2021 at 6:58