How to Concatenate Multiple Rows into One Column in MySQL
In some cases, it may be necessary to combine multiple rows into a single column. Using MySQL’s GROUP_CONCAT function, here is how to concatenate multiple rows into one column. The function can also be used in MySQL to concatenate rows into a string, or to get multiple row data in one row.
The following steps describe how to concatenate multiple rows into a single column in MySQL.
If you have the following table orders(sales_rep, sale)
1. Create A Table
#start create table orders(orders_rep varchar(255),sale int); #end
2. Insert Data
#start insert into orders(orders_rep, sale) values('Albert',10),('Bob',23), ('Chris',20),('Dave',35); #end
3. Select Table
#start Select * from orders #end
Here is a query that will return all sales reps whose sales are greater than 10.
#start select orders_rep from orders where sale>10; #end
The output would be as follows
We would like the orders_rep names to appear on a single line, however.
Bob, Chris, Dave
The GROUP_CONCAT function is used here to concatenate multiple rows into a single column.
#start select group_concat(orders_rep) from orders where sale>10; #end
The GROUP_CONCAT function concatenates all non-null values in a group and returns them as a single string.
Leave a comment
You must be logged in to post a comment.