Concatenate multiple MySQL rows to one field

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.

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!!!