95

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
  • 34,399
  • 18
  • 41
  • 57
Karunakar
  • 2,209
  • 4
  • 15
  • 20

11 Answers11

205

Check this:

SELECT GROUP_CONCAT(id)
FROM table_level
WHERE parent_id = 4
GROUP BY parent_id;
informatik01
  • 16,038
  • 10
  • 74
  • 104
naveen goyal
  • 4,571
  • 2
  • 16
  • 26
13

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;
Dharmendra Singh
  • 1,186
  • 12
  • 22
Sanal K
  • 723
  • 4
  • 14
9

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
Nishu Tayal
  • 20,106
  • 8
  • 49
  • 101
9

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
  • 10,657
  • 14
  • 59
  • 80
cksahu
  • 141
  • 1
  • 4
8

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"
Ankit Sharma
  • 3,923
  • 2
  • 29
  • 49
6

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

Rohan Khude
  • 4,455
  • 5
  • 49
  • 47
2

Use group_concat method in mysql

kamal pal
  • 4,187
  • 5
  • 25
  • 40
hepizoj
  • 243
  • 4
  • 9
1

Just so for people doing it in SQL server: use STRING_AGG to get similar results.

DPP
  • 12,716
  • 3
  • 49
  • 46
1

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.

Yogesh A Sakurikar
  • 1,731
  • 1
  • 11
  • 13
1

In some cases you do not have field, which can be used for "GROUP BY" part. You may create "fake field" for it. Sample query

SELECT GROUP_CONCAT(id), "fake" as fakeField FROM table WHERE .... GROUP BY fakeField
Andrew Zhilin
  • 1,654
  • 16
  • 11
0
SELECT GROUP_CONCAT(id) as ids FROM table_level where parent_id=4 group by parent_id;
Sandeep Sherpur
  • 2,418
  • 25
  • 27