How to achieve STRING_AGG() in MySQL database

PostgreSQL and SQL Server have the string_agg function. MySQL does not have a function with the same name, but MySQL does have the group_concat function that does the same thing.

In this tutorial, you will learn how to use the MySQL GROUP_CONCAT() function to achieve the same effect in PostgreSQL and SQL Server.

Let’s start with a demo.

Suppose we have a table named string_agg_test1 like this in MySQL:

using query

SELECT group_concat(name)
FROM test1.string_agg_test1;

Result:

+-----------------------+
|group_concat(name)     |
+-----------------------+
|Jack,Tom,Sam,Will,Peter|
+-----------------------+

If you want to use a different seprator other than comma, you can write a query like this:

SELECT group_concat(name SEPARATOR '|')
FROM test1.string_agg_test1;

and the result is:

+--------------------------------+
|group_concat(name SEPARATOR '|')|
+--------------------------------+
|Jack|Tom|Sam|Will|Peter         |
+--------------------------------+