MySQL GROUP_CONCAT() function
GROUP_CONCAT() function
MySQL GROUP_CONCAT() function returns a string with concatenated non-NULL value from a group. It is used to concatenate and aggregate values from multiple rows within a specific column into a single string. It's particularly useful for combining and displaying related data in a compact format.
Returns NULL when there are no non-NULL values.
This function is useful in -
- The primary purpose of the GROUP_CONCAT() function is to concatenate values from multiple rows into a single string.
- You can use GROUP_CONCAT() to aggregate data based on a certain column or attribute.
- The GROUP_CONCAT() can use to create a list of tags for each record.
- When working with hierarchical data structures, you can use GROUP_CONCAT() to show parent-child relationships in a readable format.
- In applications, you can use GROUP_CONCAT() to display a user's preferences, settings, or selected options in a user-friendly format.
- GROUP_CONCAT() is useful for displaying data in applications, reports, or user interfaces where a single field needs to show multiple related values.
- The function allows you to define custom separators (other than commas) and order for concatenated values, giving you flexibility in how data is presented.
Syntax:
GROUP_CONCAT(expr);
Where expr is an expression.
MySQL Version: 8.0
Contents:
Example : MySQL GROUP_CONCAT() function
The following MySQL statement will return a list of comma(,) separated 'cate_id's for each group of 'pub_id' from the book_mast table.
Sample table: book_mast
Code:
SELECT pub_id,GROUP_CONCAT(cate_id)
FROM book_mast
GROUP BY pub_id;
Output:
mysql> SELECT pub_id,GROUP_CONCAT(CATE_ID) -> FROM book_mast -> GROUP BY pub_id; +--------+-----------------------+ | pub_id | GROUP_CONCAT(CATE_ID) | +--------+-----------------------+ | P001 | CA002,CA004 | | P002 | CA003,CA003 | | P003 | CA001,CA003 | | P004 | CA005,CA002 | | P005 | CA001,CA004 | | P006 | CA005,CA001 | | P007 | CA005,CA002 | | P008 | CA005,CA004 | +--------+-----------------------+ 8 rows in set (0.02 sec)
Pictorial Presentation:
Example: MySQL GROUP_CONCAT() with order by
The following MySQL statement will return unique “cate_id”s , as a list of strings separated by the commas, in ascending order for each group of 'pub_id' from the book_mast table. The order can be changed in descending, using 'DESC' instead of 'ASC' at the end of the select statement.
Sample table: book_mast
Code:
SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id)
FROM book_mast
GROUP BY pub_id
ORDER BY GROUP_CONCAT(DISTINCT cate_id) ASC;
Output:
mysql> SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id) -> FROM book_mast -> GROUP BY pub_id -> ORDER BY GROUP_CONCAT(DISTINCT cate_id) ASC; +--------+--------------------------------+ | pub_id | GROUP_CONCAT(DISTINCT cate_id) | +--------+--------------------------------+ | P003 | CA001,CA003 | | P005 | CA001,CA004 | | P001 | CA002,CA004 | | P002 | CA003 | | P006 | CA005,CA001 | | P004 | CA005,CA002 | | P007 | CA005,CA002 | | P008 | CA005,CA004 | +--------+--------------------------------+ 8 rows in set (0.00 sec)
Example : MySQL GROUP_CONCAT() with distinct
The following MySQL statement will return the unique “cate_id”s, as a list of strings separated by the commas, for each group of 'pub_id' from the book_mast table.
Sample table : book_mast
Code:
SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id)
FROM book_mast
GROUP BY pub_id;
Output:
mysql> SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id) -> FROM book_mast -> GROUP BY pub_id; +--------+--------------------------------+ | pub_id | GROUP_CONCAT(DISTINCT cate_id) | +--------+--------------------------------+ | P001 | CA002,CA004 | | P002 | CA003 | | P003 | CA001,CA003 | | P004 | CA005,CA002 | | P005 | CA001,CA004 | | P006 | CA005,CA001 | | P007 | CA005,CA002 | | P008 | CA005,CA004 | +--------+--------------------------------+ 8 rows in set (0.00 sec)
Example : MySQL GROUP_CONCAT() with separator
The following MySQL statement will return unique “cate_id”s, as a list of strings separated by the specified separator ' '(space) in ascending order for each group of 'pub_id' from the book_mast table. The order can be changed in descending, using 'DESC' option instead of 'ASC' at the end of the select statement.
Sample table : book_mast
Code:
SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id
ORDER BY cate_id ASC SEPARATOR ' ')
FROM book_mast
GROUP BY pub_id ;
Output:
mysql> SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id -> ORDER BY cate_id ASC SEPARATOR ' ') -> FROM book_mast -> GROUP BY pub_id ; +--------+--------------------------------------------------------------------+ | pub_id | GROUP_CONCAT(DISTINCT cate_id ORDER BY cate_id ASC SEPARATOR ' ') | +--------+--------------------------------------------------------------------+ | P001 | CA002 CA004 | | P002 | CA003 | | P003 | CA001 CA003 | | P004 | CA002 CA005 | | P005 | CA001 CA004 | | P006 | CA001 CA005 | | P007 | CA002 CA005 | | P008 | CA004 CA005 | +--------+--------------------------------------------------------------------+ 8 rows in set (0.00 sec)
Previous:
COUNT(DISTINCT)
Next:
MySQL Aggregate Functions and Grouping - Max()
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics