Often database programmers and administrators need to aggregate data present in different rows of table into a single column. This is commonly required if you have a large table and you want to summarize information present in a single column. In this article, we will learn how to concatenate multiple MySQL rows into one field using group_concat() function.
How to Concatenate Multiple MySQL Rows into One Field
group_concat() is the main function specifically created to concatenate multiple MySQL rows into one field. Here is its syntax.
SELECT col1, col2, ..., colN,
GROUP_CONCAT ( [DISTINCT] col_name1 [ORDER BY clause] [SEPARATOR str_val] )
FROM table_name GROUP BY col_name2;
In the above query, we specify the usual column names of table col1, col2, etc. that need not be modified. Then we also include group_concat() function whose result will be another column in query output. In this function, we mention the column name whose values we want to concatenate. Optionally, you can include DISTINCT and ORDER BY keywords inside the function. You can also include a separator character to separate the different values of columns. Then we also add a group by clause to group the rows of the table.
Let us say you have the following sales table.
mysql> create table product(id int, name varchar(10),attribute varchar(255));
mysql> insert into product(id, name, attribute)
values(1,'A','strong'),(1,'A','durable'),
(2,'B','cheap'),(2,'B','waterproof');
mysql> select * from product;
+------+------+------------+
| id | name | attribute |
+------+------+------------+
| 1 | A | strong |
| 1 | A | durable |
| 2 | B | cheap |
| 2 | B | waterproof |
+------+------+------------+
1. Using group_concat()
Let us say you want to combine the various values of attribute column into a single row, for each product. Here is a simple SQL query to do this.
mysql> select id, name, group_concat(attribute separator ',') as 'attributes'
from product
group by id,name;
+------+------+------------------+
| id | name | attributes |
+------+------+------------------+
| 1 | A | strong,durable |
| 2 | B | cheap,waterproof |
+------+------+------------------+
In the above query, we have included id and name column in select clause as-is. We have also called group_concat() function on attribute column, and mentioned comma as separator. We have named the result of this column as attributes. Lastly, we have grouped the data by id and name since we want the row values to be combined for each group.
If you do not include GROUP BY clause in the query then all column values will be concatenated into a single string and assigned to the first row of the table.
mysql> select id, name, group_concat(attribute separator ',') as 'attributes'
from product;
+------+------+---------------------------------+
| id | name | attributes |
+------+------+---------------------------------+
| 1 | A | strong,durable,cheap,waterproof |
+------+------+---------------------------------+
2. Using distinct keyword
If there are duplicate values in the column then you can use distinct keyword to combine only duplicate values in them. Let us say you have the following product table.
mysql> select * from product order by id, name;
+------+------+------------+
| id | name | attribute |
+------+------+------------+
| 1 | A | strong |
| 1 | A | durable |
| 1 | A | attractive |
| 1 | A | strong |
| 2 | B | cheap |
| 2 | B | waterproof |
| 2 | B | attractive |
| 2 | B | cheap |
+------+------+------------+
As you can see, there are duplicate rows in our table. If we try to combine the values of attribute column using group_concat() function, we will get the following result.
mysql> select id, group_concat(attribute separator ',') as 'attributes'
from product
group by id;
+------+-----------------------------------+
| id | attributes |
+------+-----------------------------------+
| 1 | strong,durable,attractive,strong |
| 2 | cheap,waterproof,attractive,cheap |
+------+-----------------------------------+
As you can see, the result of group_concat() function contains duplicate values. If you want to avoid this and pick only unique values, then add DISTINCT keyword in group_concat() function.
mysql> select id, group_concat(distinct attribute separator ',') as 'attributes'
from product
group by id;
+------+-----------------------------+
| id | attributes |
+------+-----------------------------+
| 1 | strong,durable,attractive |
| 2 | cheap,waterproof,attractive |
+------+-----------------------------+
When you use DISTINCT keyword in group_concat() function, it will first pick only the unique values of its column and then it will concatenate the values.
3. Using order by clause
By default, group_concat() concatenates column values in the same order as they appear in original table. Let us say you have the following product table.
mysql> select * from product order by id, name;
+------+------+------------+
| id | name | attribute |
+------+------+------------+
| 1 | A | strong |
| 1 | A | durable |
| 1 | A | attractive |
| 2 | B | cheap |
| 2 | B | waterproof |
| 2 | B | attractive |
+------+------+------------+
If you use group_concat() to combine values in attribute column for each product name, then you will get the following result.
mysql> select id, group_concat(attribute separator ',') as 'attributes'
from product
group by id;
+------+-----------------------------+
| id | attributes |
+------+-----------------------------+
| 1 | strong,durable,attractive |
| 2 | cheap,waterproof,attractive |
+------+-----------------------------+
Let us say you want to display the attributes in alphabetical order instead. For this purpose, you can use the order by clause within group_concat() function as shown below.
mysql> select id, group_concat(attribute order by attribute separator ',') as 'attributes'
from product
group by id;
+------+-----------------------------+
| id | attributes |
+------+-----------------------------+
| 1 | attractive,durable,strong |
| 2 | attractive,cheap,waterproof |
+------+-----------------------------+
As you can see, the attribute column’s values have been sorted alphabetically for each group, before concatenation.
4. With concat() and group_concat()
So far, we have seen examples where we concatenate the values of only 1 field. What if you want to combine the values of 2 or more columns? In this case, you will need to use group_concat() function along with concat() function. Here is an example to concatenate all values of attribute as well as id columns.
mysql> SELECT GROUP_CONCAT( attributes SEPARATOR ' ') as "id : attributes"
FROM (
SELECT id, CONCAT ( name, ':', GROUP_CONCAT(attribute SEPARATOR', ') )
as "attributes"
FROM product
GROUP BY name
) as emp;
+-------------------------------------------------------------------------------+
| id : attributes |
+-------------------------------------------------------------------------------+
| A:strong, durable, attractive, strong B:cheap, waterproof, attractive, cheap |
+-------------------------------------------------------------------------------+
In the above query, there are 2 SQL queries. The inner SQL query does a concatenation of attribute column as well as product name column.
mysql> SELECT id, CONCAT ( name, ':', GROUP_CONCAT(attribute SEPARATOR', ') )
as "attributes"
FROM product
GROUP BY name ;
+------+----------------------------------------+
| id | attributes |
+------+----------------------------------------+
| 1 | A:strong, durable, attractive, strong |
| 2 | B:cheap, waterproof, attractive, cheap |
+------+----------------------------------------+
The outer SQL query concatenates the values of attributes column.
+-------------------------------------------------------------------------------+
| id : attributes |
+-------------------------------------------------------------------------------+
| A:strong, durable, attractive, strong B:cheap, waterproof, attractive, cheap |
+-------------------------------------------------------------------------------+
We have used a very simple example in this case for ease of understanding. You can obviously use group by clause in outer SQL query to concatenate values for each group.
Setting group_concat max length
By default, the result of group_concat() column is 1024 bytes. If it is more than then it will be truncated to 1024 bytes. If you want to increase this value, then run the following command for this purpose. Here is the query to increase the limit of group_concat() function’s result to 2048 bytes.
mysql> SET group_concat_max_len = 2048;
Conclusion
In this article, we have learnt how to combine column values of multiple rows into single row, using group_concat() function. We have also learnt about the different options available to concatenate multiple MySQL rows into one field. You can use any of them as per your requirement. group_concat() function is very useful as it provides many options such as delimiters, ordering, and unique values.
Also read:
How to See Foreign Keys Related to Table in MySQL
How to Reset Auto Increment in MySQL
How to List Database Users in MySQL
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.