How to Concatenate Multiple Rows into One Column in MySQL

Last updated on May 17th, 2020 at 06:26 am

Sometimes you might need to combine multiple rows into one column. Here’s how to concatenate multiple rows into one column in MySQL using GROUP_CONCAT function. You can also use it to concatenate rows into string, or get multiple row data in single row in MySQL.

How to Concatenate Multiple Rows into One Column in MySQL

Here are the steps to concatenate multiple rows into one column in MySQL.

Let’s say you have the following table sales(sales_rep, sale)

mysql> create table sales(sales_rep varchar(255),sale int);

mysql> insert into sales(sales_rep, sale) 
       values('Albert',10),('Bob',23),
             ('Chris',20),('Dave',35);

mysql> select * from sales;

+-----------+------+
| sales_rep | sale |
+-----------+------+
| Albert    |   10 |
| Bob       |   23 |
| Chris     |   20 |
| Dave      |   35 |
+-----------+------+

Bonus Read : How to Calculate Running Total in MySQL

Let’s say you want to report all sales reps whose sale>10 with the following query.

mysql> select sales_rep from sales where sale>10;

You would get the following output

+-----------+
| sales_rep |
+-----------+
| Bob       |
| Chris     |
| Dave      |
+-----------+

However, we want the sales_rep names to be present in a single line.

Bob, Chris, Dave

In this case, we use GROUP_CONCAT function to concatenate multiple rows into one column.

mysql> select group_concat(sales_rep) from sales2 where sale>10;
+-------------------------+
| group_concat(sales_rep) |
+-------------------------+
| Bob,Chris,Dave          |
+-------------------------+

GROUP_CONCAT concatenates all non-null values in a group and returns them as a single string.

If you want to avoid duplicates, you can also add DISTINCT in your query. Let’s say you have the following table.

mysql> select * from sales3;
+---------+------------+------+
| product | order_date | sale |
+---------+------------+------+
| A       | 2020-01-01 |   20 |
| B       | 2020-01-02 |   25 |
| B       | 2020-01-03 |   15 |
| A       | 2020-01-04 |   30 |
| A       | 2020-01-05 |   20 |
+---------+------------+------+

In the above table, the column product contains duplicate values. So we use DISTINCT keyword to pick only unique values.

mysql> select group_concat(distinct product) from sales3 where sale>10;
+--------------------------------+
| group_concat(distinct product) |
+--------------------------------+
| A,B                            |
+--------------------------------+

Bonus Read: How to Add Total Row in MySQL

You can also use GROUP BY function to concatenate row values for each group. Here’s an example. Let’s say you have the following table.

mysql> create table sales_reps(product varchar(255),sales_rep varchar(255));

mysql> insert into sales_reps(product, sales_rep) 
       values('A','Albert'),('A','Bob'),('A','Chris'),
             ('B','Dave'),('B','Edgar'),('B','Finch');

mysql> select * from sales_reps;
+---------+-----------+
| product | sales_rep |
+---------+-----------+
| A       | Albert    |
| A       | Bob       |
| A       | Chris     |
| B       | Dave      |
| B       | Edgar     |
| B       | Finch     |
+---------+-----------+

Here’s a query where we concatenate rep names for each product, using GROUP BY clause.

mysql> select product, group_concat(sales_rep) from sales_reps group by product;
+---------+-------------------------+
| product | group_concat(sales_rep) |
+---------+-------------------------+
| A       | Albert,Bob,Chris        |
| B       | Dave,Edgar,Finch        |
+---------+-------------------------+

After you concatenate multiple rows into one column, you can use a reporting tool to plot the result in a table and share them with your team. Here’s an example of a table created using Ubiq.