MySQL GROUP_CONCAT Function with Examples

How to Use the MySQL GROUP_CONCAT Function

In this tutorial, you will learn how to use the MySQL GROUP_CONCAT function, which returns a string with concatenated values from a group and returns NULL if there are no non-NULL values.

Learn more:

• Concatenate strings in MySQL
• GROUP BY clause in MySQL
• MySQL ORDER BY clause
• SELECT statement in MySQL

MySQL GROUP_CONCAT Syntax

The syntax of the GROUP_CONCAT function is as follows:

GROUP_CONCAT([DISTINCT] expression

[ORDER BY expression] [SEPARATOR string_value]);

Remarks

• If you want to eliminate duplicate values, use the DISTINCT clause.
• To sort the values in the result in ascending or descending order, use the ORDER BY clause.
• The default separator between values in a group is a comma (,). If you need to specify it explicitly, use SEPARATOR, followed by the string value. However, if you need to eliminate the separator, use SEPARATOR ("")

GROUP_CONCAT Examples

The following is a table of orders:

Example 1: Return a comma (,) separated concatenated string

The following statement returns a comma (,) separated concatenated string of the results of the order_number column of the orders table:

SELECT GROUP_CONCAT(o.order_number ORDER BY o.order_number DESC)

FROM orders o;

Result:

ORD_10008,#ORD_10007,#ORD_10006,#ORD_10005,#ORD_10004,#ORD_10003,#ORD_10002,#ORD_10001

Here, the SEPARATOR clause is not specified, so the default separator, a comma, is used between the values in the group.

Example 2: Return a vertical bar (|) separated concatenated string

The following statement returns the result of concatenating the order_number columns of the orders table, separated by vertical bars (|):

SELECT GROUP_CONCAT(o.order_number ORDER BY o.order_number DESC SEPARATOR " | ")

FROM orders o;

Here, as you can see, the SEPARATOR clause specified (|), the vertical bar separator is used between values in a group.

Result:

ORD_10008 | #ORD_10007 | #ORD_10006 | #ORD_10005 | #ORD_10004 | #ORD_10003 | #ORD_10002 | #ORD_10001

Example 3: Return a list of a vertical bar (|) separated concatenated strings (1)

The following statement returns a list of a vertical bar (|) separated, concatenated string result of the order_number column for each group of a salesperson from the orders table:

SELECT o.salesperson, GROUP_CONCAT(o.order_number ORDER BY o.order_number DESC SEPARATOR " | ")

FROM orders o

GROUP BY o.salesperson;

Result:

Example 4: Return a list of a vertical bar (|) separated concatenated strings (2)

The following statement returns a list of a vertical bar (|) separated, concatenated string result of the customer_name column for each group of a salesperson from the orders table:

SELECT o.salesperson, GROUP_CONCAT(o.customer_name ORDER BY o.customer_name ASC SEPARATOR " | ") AS Result1

FROM orders o

GROUP BY o.salesperson;

Result:

Example 5: Return a list of a vertical bar (|) separated concatenated strings (distinct values)

The following statement returns a list of a vertical bar (|) separated, concatenated string result of the customer_name for each group of a salesperson from the orders table:

SELECT o.salesperson, GROUP_CONCAT(DISTINCT o.customer_name ORDER BY o.customer_name ASC SEPARATOR " | ") AS Result1

FROM orders o

GROUP BY o.salesperson;

Result:

Here, the DISTINCT clause is used so that duplicate values are removed from the results for each group of salespeople.

Summary

In this tutorial, you have learned how to return a concatenated string using the MySQL GROUP_CONCAT function.