Row to Column

GROUP_CONCAT() function

MySQL GROUP_CONCAT() function returns a string with concatenated non-NULL value from a group.

Returns NULL when there are no non-NULL values.

Syntax:

GROUP_CONCAT(expr);

Where expr is an expression.

MySQL Version: 5.6

Contents:

• Example : MySQL GROUP_CONCAT() function
• MySQL GROUP_CONCAT() with order by and example
• MySQL GROUP_CONCAT() with distinct example
• MySQL GROUP_CONCAT() with separator and example

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;

Copy

Sample 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)

Credit to https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php