This article will see how to concatenate the MySQL query results from multiple rows into a single field through many examples. We can also say that we want to see the results from the MySQL query into a single-row column. We will be using the GROUP_CONCAT() function of MySQL in the below examples.
GROUP_CONCAT(expression) will return the concatenated values from a group. This function will return the result ignoring the NULL values.
Let us get started by making the sample data. We will be creating a table sale_details followed by inserting a few rows into it.
# create table sale_details CREATE TABLE sale_details ( id INT , sale_person_id VARCHAR(255) , sale_person_name VARCHAR(255), no_products_sold INT, sales_department VARCHAR(255) ); #inserting into sale_details INSERT INTO sale_details (id,sale_person_id,sale_person_name,no_products_sold,sales_department) VALUES(1,"sd1","Henry",2000,"Kitchen Essentials"), (2,"sd1","Henry",5000,"Apparels"), (3,"sd1","Henry",40,"Medicines"), (4,"sd2","Richa",3000,"Furniture"), (5,"sd2","Richa",500,"Bathroom Essentials"), (6,"sd2","Richa",50,"Shoes"), (7,"sd3","Ved",100,"Kitchen Essentials"), (8,"sd3","Ved",150,"Apparels"), (9,"sd3","Ved",1000,"Medicines"), (10,"sd4","George",600,"Bathing Essentials"), (11,"sd4","George",1200,"Shoes"), (12,"sd4","George",200,"Medicines"), (13,"sd5","Alexendra",487, NULL), (14,"sd5","Alexendra",570,"Furniture"), (15,"sd5","Alexendra",510,"Medicines"), (16,"sd6","Gustav",3000,"Kitchen Essentials"), (17,"sd6","Gustav",100,"Shoes"), (18,"sd6","Gustav",150,"Furniture");
To view the snapshot of the sale_details table, execute:
SELECT * FROM sale_details;
Output:-

We will be looking into a few examples to have a better understanding of the concept.
Example1: Select all the sales departments for each salesperson from the sale_details table.
Frequently Asked:
- MYSQL SELECT WHERE MONTH AND YEAR
- MySQL : Change Datatype of a Column
- MySQL: Insert in a loop
- MySQL create table if not exists
Observe the below code for the solution.
SELECT sale_person_name, GROUP_CONCAT(sales_department ORDER BY sales_department ASC SEPARATOR ', ') AS sale_departments FROM sale_details GROUP BY sale_person_name;
Output:-

Explanation:-
- The above query used the GROUP_CONCAT() function to concatenate all sale_department values for which a salesperson is working separated by a ‘,.’
- The NULL value from the sale_department table is not a part of the final result.
- Since we are using a GROUP BY clause, the query returns the result for each salesperson.
Example2: Select all the sale departments in a single field from the sale_details table.
Observe the below code for the solution.
SELECT GROUP_CONCAT(DISTINCT sales_department SEPARATOR ' , ') AS sale_departments FROM sale_details;
Output:-

Explanation:-
- The above query has used GROUP_CONCAT() function to concatenate all the sale_department values in the sale_details table.
- The NULL value from the sale_department table is not a part of the final result.
- Since we are using a DISTINCT clause, the query is not returning the duplicate values.
Example3: Select all the sale departments in a single field from the sale_details table only for Henry and George.
Observe the below code for the solution.
SELECT GROUP_CONCAT(DISTINCT sales_department SEPARATOR ' , ') AS sale_departments FROM sale_details WHERE sale_person_name IN ('Henry' , 'George');
Output:-

Explanation:-
- The above query has used GROUP_CONCAT() function to concatenate all the sale_department values for salesperson Henry and George.
- The DISTINCT clause avoids duplicates.
- IN operator is used to get the result only for Henry and George.
READ MORE:
- What are triggers in MySQL
- What are views in MySQL
- What are indexes in MySQL
- MySQL Select last N rows
- MySQL select first row in each group
We hope this article helped you with MySQL queries to concatenate multiple row values into a single field. Good Luck!!!