MySQL GROUP_CONCAT Function with Examples


How to Use the MySQL GROUP_CONCAT Function

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

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.
  • You can use the ORDER BY clause to sort values, ascending or descending, in the result.
  • The default separator between values in a group is a comma (,). If you need to specify a separator explicitly, then use SEPARATOR, followed by a string value. However, if you need to eliminate the separator, specify SEPARATOR "".

GROUP_CONCAT Examples

The following is the demo orders table:

order_idcustomer_codecustomer_nameorder_numberorder_dateamountsalesperson
1#CID_10001Customer A#ORD_100012019-03-011890.50Employee A
2#CID_10001Customer A#ORD_100022019-03-012300.00Employee A
3#CID_10003Customer C#ORD_100032019-03-011850.50Employee B
4#CID_10004Customer D#ORD_100042019-03-021240.50Employee C
5#CID_10004Customer D#ORD_100052019-03-022200.00Employee C
6#CID_10006Customer F#ORD_100062019-03-033200.80Employee A
7#CID_10007Customer G#ORD_100072019-03-041280.00Employee B
8#CID_10008Customer H#ORD_100082019-03-04980.00Employee C

Example 1: Return a Comma (,) Separated Concatenated String

The following statement returns a comma (,) separated, concatenated string result of the order_number column from 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, since the SEPARATOR clause not specified, the default separator comma is used between values in a group.

Example 2: Return a Vertical Bar (|) Separated Concatenated String

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

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 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:

salespersonResult1
Employee AORD_10006 | #ORD_10002 | #ORD_10001
Employee BORD_10007 | #ORD_10003
Employee CORD_10008 | #ORD_10005 | #ORD_10004

Example 4: Return a List of 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:

salespersonResult1
Employee ACustomer A | Customer A | Customer F
Employee BCustomer C | Customer G
Employee CCustomer D | Customer D | Customer H

Example 5: Return a List of 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:

salespersonResult1
Employee ACustomer A | Customer F
Employee BCustomer C | Customer G
Employee CCustomer D | Customer H

Here, since the DISTINCT clause used, duplicate values removed from the result for each group of a salesperson.

In this tutorial, you've learned how to use the MySQL GROUP_CONCAT function. GROUP_CONCAT() returns a string with concatenated values from a group and returns NULL if there are no non-NULL values.

Leave a Reply

Your email address will not be published. Required fields are marked *