How to use GROUP BY to concatenate strings in MySQL and how to set a separator for the concatenation?

To concatenate strings in MySQL with GROUP BY, you need to use GROUP_CONCAT() with a SEPARATOR parameter which may be comma(‘) or space (‘ ‘) etc.

The syntax is as follows:

SELECT yourColumnName1,GROUP_CONCAT(yourColumnName2 SEPARATOR ‘yourValue’) as anyVariableName FROM yourTableName GROUP BY yourColumnName1;

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table GroupConcatenateDemo
   -> (
   -> Id int,
   -> Name varchar(20)
   -> );
Query OK, 0 rows affected (0.99 sec)

Insert some records in the table using insert command. The query to insert record is as follows:

mysql> insert into GroupConcatenateDemo values(10,'Larry');
Query OK, 1 row affected (0.41 sec)
mysql> insert into GroupConcatenateDemo values(11,'Mike');
Query OK, 1 row affected (0.18 sec)
mysql> insert into GroupConcatenateDemo values(12,'John');
Query OK, 1 row affected (0.14 sec)
mysql> insert into GroupConcatenateDemo values(10,'Elon');
Query OK, 1 row affected (0.63 sec)
mysql> insert into GroupConcatenateDemo values(10,'Bob');
Query OK, 1 row affected (0.12 sec)
mysql> insert into GroupConcatenateDemo values(11,'Sam');
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from GroupConcatenateDemo;

The following is the output:

+------+-------+
| Id   | Name  |
+------+-------+
|   10 | Larry |
|   11 | Mike  |
|   12 | John  |
|   10 | Elon  |
|   10 | Bob   |
|   11 | Sam   |
+------+-------+
6 rows in set (0.00 sec)

Here is the query that use GROUP BY to concatenate the strings in MySQL. Perform GROUP BY on the basis of Id and concatenate the strings using GROUP_CONCAT() function in MySQL.

The query is as follows:

mysql> select Id,group_concat(Name SEPARATOR ',') as GroupConcatDemo from GroupConcatenateDemo
   -> group by Id;

The following is the output:

+------+-----------------+
| Id   | GroupConcatDemo |
+------+-----------------+
|   10 | Larry,Elon,Bob  |
|   11 | Mike,Sam        |
|   12 | John            |
+------+-----------------+
3 rows in set (0.00 sec)

Ankith Reddy

Updated on 30-Jul-2019 22:30:24

• Related Questions & Answers
• How to concatenate strings in R?
• How to use order by, group by in c#?
• Group concatenate the last name from a MySQL column and set a condition to display limited records
• How to update a value with substring of current value by removing the separator and numbers after a separator in MySQL?
• How to concatenate strings using both GROUP_CONCAT() and CONCAT() in the same MySQL query?
• How to concatenate several strings in JavaScript?
• How to concatenate two strings in C#?
• How to concatenate two strings in Python?
• How to concatenate two strings in Golang?
• How to correctly concatenate strings in Kotlin?
• How to implement GROUP by range in MySQL?
• How to concatenate two strings using Java?
• How to determine if a value appears in a GROUP BY group in MySQL?
• How can I set a MySQL database to use MyISAM by default?
• Perform MongoDB array concatenation to concatenate records