{"id":2530,"date":"2013-05-19T17:59:01","date_gmt":"2013-05-20T01:59:01","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=2530"},"modified":"2023-11-09T17:59:45","modified_gmt":"2023-11-10T00:59:45","slug":"mysql-group_concat-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-aggregate-functions\/mysql-group_concat-function\/","title":{"rendered":"MySQL GROUP_CONCAT() Function"},"content":{"rendered":"\n

Summary<\/b>: in this tutorial, you will learn how to use the MySQL GROUP_CONCAT()<\/code> function to concatenate strings from a group with various options.<\/p>\n\n\n\n

Introduction to MySQL GROUP_CONCAT() function<\/h2>\n\n\n\n

The MySQL GROUP_CONCAT()<\/code> function is an aggregate function<\/a> that concatenates strings from a group into a single string with various options.<\/p>\n\n\n\n

The following shows the syntax of the GROUP_CONCAT()<\/code> function:<\/p>\n\n\n

GROUP_CONCAT(\n    DISTINCT expression\n    ORDER BY expression\n    SEPARATOR sep\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example demonstrates how the GROUP_CONCAT()<\/code> function works.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> t (\n    v CHAR<\/span>\n);\n\nINSERT<\/span> INTO<\/span> t(v) VALUES<\/span>('A'<\/span>),('B'<\/span>),('C'<\/span>),('B'<\/span>);\n\nSELECT<\/span> \n    GROUP_CONCAT<\/span>(DISTINCT<\/span> v\n        ORDER<\/span> BY<\/span> v ASC<\/span>\n        SEPARATOR ';'<\/span>)\nFROM<\/span>\n    t;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
\"MSQL<\/figure>\n\n\n\n
The DISTINCT<\/code> clause allows you to eliminate duplicate values in the group before concatenating them.<\/p>\n\n\n\n
The ORDER BY<\/code> clause allows you to sort the values in ascending or descending order before concatenating. By default, it sorts the values in ascending order. If you want to sort the values in the descending order, you need to specify explicitly the DESC<\/code> option.<\/p>\n\n\n\n
The SEPARATOR<\/code> specifies a literal value inserted between values in the group. If you do not specify a separator, the GROUP_CONCAT<\/code> function uses a comma (,<\/code>) as the default separator.<\/p>\n\n\n\n
The GROUP_CONCAT<\/code> function ignores NULL<\/code> values. It returns NULL<\/code> if there are no matching rows or all arguments are NULL<\/code> values.<\/p>\n\n\n\n
The GROUP_CONCAT<\/code> function returns a binary or non-binary string, which depends on the arguments. <\/p>\n\n\n\n
By default, the maximum length of the return string is 1024. If you need more than this, you can extend the maximum length by setting the group_concat_max_len<\/code> system variable at SESSION<\/code> or GLOBAL<\/code> level.<\/p>\n\n\n\n
MySQL GROUP_CONCAT() function examples<\/h2>\n\n\n\n
Let’s take a look at the customers<\/code> table in the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To get all countries where customers are located as a comma-separated string, you use the GROUP_CONCAT()<\/code> function as follows:<\/p>\n\n\n
SELECT<\/span> \n    GROUP_CONCAT<\/span>(country)\nFROM<\/span>\n    customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
However, some customers are located in the same country. To remove the duplicate country’s names, you add the DISTINCT<\/code> clause as the following query:<\/p>\n\n\n
SELECT<\/span> \n    GROUP_CONCAT<\/span>(DISTINCT<\/span> country)\nFROM<\/span>\n    customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
It is more readable if the country’s names are in ascending order. To sort the country’s name before concatenating, you use the ORDER BY<\/code> clause as follows:<\/p>\n\n\n
SELECT<\/span> \n    GROUP_CONCAT<\/span>(DISTINCT<\/span> country\n        ORDER<\/span> BY<\/span> country)\nFROM<\/span>\n    customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
To change the default separator of the returned string from a comma (,) to a semi-colon (;), you use the SEPARATOR<\/code> clause as the following query:<\/p>\n\n\n
SELECT<\/span> \n    GROUP_CONCAT<\/span>(DISTINCT<\/span> country\n        ORDER<\/span> BY<\/span> country\n        SEPARATOR ';'<\/span>)\nFROM<\/span>\n    customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Great! now you know how the GROUP_CONCAT()<\/code> function works. Let’s put it in a practical example.<\/p>\n\n\n\n
Each customer has one or more sales representatives. In other words, each sales employee is in charge of one or more customers. To find out who is in charge of which customers, you use the inner join<\/a> clause as follows:<\/p>\n\n\n
SELECT<\/span> \n    employeeNumber, \n    firstname, \n    lastname, \n    customername\nFROM<\/span>\n    employees\n        INNER<\/span> JOIN<\/span>\n    customers ON<\/span> customers.salesRepEmployeeNumber = employees.employeeNumber\nORDER<\/span> BY<\/span> \n\tfirstname, \n    lastname;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"GROUP_CONCAT<\/figure>\n\n\n\n
Now, we can group the result set by the employee number and concatenate all employees that are in charge of the employee by using the GROUP_CONCAT()<\/code> function as follows:<\/p>\n\n\n
SELECT<\/span> \n    employeeNumber,\n    firstName,\n    lastName,\n    GROUP_CONCAT<\/span>(DISTINCT<\/span> customername\n        ORDER<\/span> BY<\/span> customerName)\nFROM<\/span>\n    employees\n        INNER<\/span> JOIN<\/span>\n    customers ON<\/span> customers.salesRepEmployeeNumber = employeeNumber\nGROUP<\/span> BY<\/span> employeeNumber\nORDER<\/span> BY<\/span> firstName , lastname;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"GROUP_CONCAT<\/figure>\n\n\n\n
The result set is much easier to read.<\/p>\n\n\n\n
Using GROUP_CONCAT() with CONCAT_WS() function example<\/h3>\n\n\n\n
Sometimes, the GROUP_CONCAT<\/code> function can be combined with the CONCAT_WS<\/a> function to make the result of the query more useful.<\/p>\n\n\n\n
For example, to make a list of semicolon-separated values of customers:<\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n