Multi Group_Concat in a single query with Distinct – MySQL

The CONCAT() function joins the input strings or different column values and returns as a single string. However, if we need to concatenate rows of strings (rows from the same column) into a single string with a desired separator, this will not work.

There comes a function GROUP_CONCAT that fulfills this requirement and this function known with different names in various RDBMS and Big Data Technologies like Hive.

GROUP_CONCAT() is a function which merges the data from multiple rows into one field. It is a GROUP BY function which returns a string. Comma (,) or any user specified delimiter can be used to separate the values in the string.

To know more about this function and similar functionality in various RDBMS and big data technologies, please click here.

We’ll look at how to use the GROUP CONCAT function with the GROUP clause in this post. This is used to combine the categorized values into a single row for each category.

Consider the below dataset.

+-----------+-------+------------+
|     IdCol |  ProID| ReleaseYear|
+-----------+-------+------------+
|      1001 |   106 |       2020 |
|      1001 |   294 |       2020 |
|      1001 |   119 |       2020 |
|      1001 |   111 |       2020 |
|      1024 |   877 |       2022 |
|      1024 |   988 |       2022 |
|      1024 |   766 |       2022 |
|      1024 |   655 |       2022 |
|      1019 |   544 |       2021 |

The output required for the above dataset is-

+-----------+------------------+----------------+
|     IdCol |      ProID       |  ReleaseYear   |
+-----------+------------------+----------------+
|      1001 | 106,294,119,111  |          2020  |
|      1019 |             544  |          2021  |
|      1024 | 877,988,766,655  |          2022  |
+-----------+------------------+----------------+

For Practice:
Creating the table:
CREATE TABLE SomeTestTab (
IdCol INT,
ProdID INT,
ReleaseYear INT );

Data Insertion:
INSERT INTO SomeTestTab VALUES
(1001,106,2020),
(1001,294,2020),
(1001,119,2020),
(1001,111,2020),
(1024,877,2022),
(1024,988,2022),
(1024,766,2022),
(1024,655,2022),
(1019,544,2021);

Query to concatenate:
SELECT IdCol,
GROUP_CONCAT(ProdID) AS ProdIDs,
GROUP_CONCAT(DISTINCT ReleaseYear) AS ReleaseYear
FROM SomeTestTab GROUP BY IdCol;

MySQL_Group_Concat