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:
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:
order_id | customer_code | customer_name | order_number | order_date | amount | salesperson |
1 | #CID_10001 | Customer A | #ORD_10001 | 2019-03-01 | 1890.50 | Employee A |
2 | #CID_10001 | Customer A | #ORD_10002 | 2019-03-01 | 2300.00 | Employee A |
3 | #CID_10003 | Customer C | #ORD_10003 | 2019-03-01 | 1850.50 | Employee B |
4 | #CID_10004 | Customer D | #ORD_10004 | 2019-03-02 | 1240.50 | Employee C |
5 | #CID_10004 | Customer D | #ORD_10005 | 2019-03-02 | 2200.00 | Employee C |
6 | #CID_10006 | Customer F | #ORD_10006 | 2019-03-03 | 3200.80 | Employee A |
7 | #CID_10007 | Customer G | #ORD_10007 | 2019-03-04 | 1280.00 | Employee B |
8 | #CID_10008 | Customer H | #ORD_10008 | 2019-03-04 | 980.00 | Employee C |
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:
salesperson | Result1 |
Employee A | ORD_10006 | #ORD_10002 | #ORD_10001 |
Employee B | ORD_10007 | #ORD_10003 |
Employee C | ORD_10008 | #ORD_10005 | #ORD_10004 |
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:
salesperson | Result1 |
Employee A | Customer A | Customer A | Customer F |
Employee B | Customer C | Customer G |
Employee C | Customer D | Customer D | Customer H |
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:
salesperson | Result1 |
Employee A | Customer A | Customer F |
Employee B | Customer C | Customer G |
Employee C | Customer D | Customer H |
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.