How to Concatenate Multiple Rows into One Field in MySQL?

Although many rows of data are typically stored in MySQL, there are instances in which we would prefer to consolidate numerous rows into a single column. MySQL offers several ways to accomplish this, including JSON_ARRAYAGG() for structured output and GROUP_CONCAT(). In this blog, let us explore how we can concatenate multiple rows into one field in MySQL in detail with examples for each.

Table of Contents:

Why is it necessary to Concatenate Several Rows into a Single Field?

While exporting data in a format with a more structured output, such as CSV or JSON, concatenating rows into a single field in MySQL avoids duplicate rows and gathers relevant data valuable for more legible reports. By making recovery less complicated, the method makes the query run more smoothly. Data presentation is improved by using a single summarized row rather than many. GROUP_CONCAT(), the most used MySQL technique, enables concatenation with a user-specified separator.

Advantages of Concatenating Multiple Rows into One Field

• More Readable: Related data will be displayed on one single line, making it easier to understand when generating reports or queries of a result.
• Improved Reporting: Report generation becomes simpler due to the ability to group similar values into a simple format.
• Complex Queries: To optimize the performance, joins and subqueries are not preferred.

Methods to Concatenate Multiple Rows into One Field in MySQL

Before introducing the methods to concatenate multiple rows into one field, let us create a table called Orders that can be used as an example of the following methods.

--Create an Orders table
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    product_name VARCHAR(100)
);
-- Insert some values into it
INSERT INTO orders (customer_id, product_name) VALUES
(1, 'Laptop'),
(1, 'Mouse'),
(1, 'Keyboard'),
(2, 'Monitor'),
(2, 'Desk'),
(3, 'Phone'),
(3, 'Charger'),
(3, 'Headphones');

Method 1: Using GROUP_CONCAT() in MySQL

An aggregate method in MySQL called GROUP_CONCAT() concatenates several rows into a single string that is grouped by a certain field.

Syntax:

GROUP_CONCAT(expression)

Example:

SELECT customer_id, GROUP_CONCAT(product_name) AS products FROM orders GROUP BY customer_id;

Output:

Explanation: Here, the GROUP_CONCAT() combines product names for each CustomerID into a single string, separated by commas.

Method 2: Using ORDER BY inside GROUP_CONCAT() in MySQL

To preserve a certain order, this function arranges variables inside GROUP_CONCAT() prior to concatenation. 

Syntax

GROUP_CONCAT(expression ORDER BY expression)

Example:

SELECT customer_id, GROUP_CONCAT(product_name ORDER BY product_name) AS products

FROM orders

GROUP BY customer_id;

Output:

Explanation: Here, the GROUP_CONCAT(product_name) aggregates multiple product names for each customer into a single string. 

Method 3: Using GROUP_CONCAT() with Multiple Columns in MySQL

Combines the values of several columns within GROUP_CONCAT() to concatenate them.

Syntax:

GROUP_CONCAT(CONCAT(col1, 'text', col2))

Example:

SELECT customer_id, GROUP_CONCAT(CONCAT(product_name, ' (Order ID: ', order_id, ')') ORDER BY product_name SEPARATOR ', ') AS products
FROM orders
GROUP BY customer_id;

Output:

Explanation: Here, the GROUP_CONCAT(CONCAT(…)) function is used to concatenate multiple row values into a single string for each group. The CONCAT() formats each row by appending the order ID next to the product name.

Method 4: Using GROUP_CONCAT() with HAVING Clause in MySQL

The HAVING Clause is used to filter the grouped results according to the concatenated output.

Syntax:

HAVING concatenated_column LIKE '%value%'

Example:

SELECT customer_id, GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') AS products
FROM orders
GROUP BY customer_id
HAVING products LIKE '%Mouse%';

Output:

Explanation: Here, the HAVING Clause filters the results to customers who have at least one product containing the word “Mouse”. %Mouse% states that the word Mouse can appear anywhere in the concatenated product list.

Method 5: Using Basic JSON_ARRAYAGG() in MySQL

Instead of combining the multiple-row data into a string separated by commas, this function aggregates them into a structured JSON array.

Syntax:

JSON_ARRAYAGG(expression)

Example:

SELECT customer_id, JSON_ARRAYAGG(product_name) AS products_json
FROM orders
GROUP BY customer_id;

Output:

Explanation: Here, the JSON_ARRAYAGG(product_name) function outputs a structured JSON array of product_name.

Method 6: Using Variables (@var) for Row-by-Row Concatenation in MySQL

Iteratively concatenating values row by row without the usage of GROUP_CONCAT is possible with MySQL session variables.

Syntax:

SET @var = '';  
SELECT @var := CONCAT(@var, column_name, 'separator') FROM table_name;  
SELECT @var AS concatenated_result;

Example:

SET @product_list = '';  
SELECT @product_list := CONCAT(@product_list, product_name, ', ')  
FROM orders;  
SELECT TRIM(TRAILING ', ' FROM @product_list) AS products;

Output:

Explanation: Here, values are appended to @product_list as the SELECT statement iterates over each entry. The “SELECT @product_list AS products” displays the final concatenated result.

Performance Comparison of Each Method

Method	Use Case	Pros	Cons
Using GROUP_CONCAT()	Simple row concatenation	Efficiently handles both big and small datasets.	Unable to work with hierarchical structures
Using ORDER BY inside GROUP_CONCAT()	Ordered concatenation	Ensures order of concatenated values	Indexing is required for performance optimization
Using GROUP_CONCAT with multiple columns	Concatenating multiple columns within the same row	Allows formatting with additional details	More complex query syntax
Using GROUP_CONCAT() with HAVING Clause	Filtering concatenated results	Allows filtering after concatenation	It has an impact on the performance when working with large datasets
Using JSON_ARRAYAGG()	Returning JSON array format for applications	Structured JSON output	Slower than GROUP_CONCAT
Using Variables(@var)	Concatenating values row-by-row without GROUP_CONCAT	Works when GROUP_CONCAT() is not feasible	Slower than GROUP_CONCAT()

Real-world Examples

1. E-Commerce Store

To show all the products bought by each customer in one row, the e-commerce store has prepared a query. 

Example:

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    product_name VARCHAR(255)
);
INSERT INTO orders (customer_id, product_name) VALUES
(1, 'Laptop'), (1, 'Mouse'), (1, 'Keyboard'),
(2, 'Monitor'), (2, 'Desk'), (3, 'Headphones');
SELECT customer_id, 
       GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') AS products
FROM orders
GROUP BY customer_id;

Output:

Explanation: Here, the GROUP_CONCAT() groups products by customer_id and concatenates them in one field. The ORDER BY inside the GROUP_CONCAT() ensures an alphabetical order of products.

2. Employee Management System

A company is interested in storing multiple skills of employees in a single row so that it can have easy access to the information. 

Example:

CREATE TABLE employee_skills (
    emp_id INT,
    skill VARCHAR(255)
);
INSERT INTO employee_skills (emp_id, skill) VALUES
(101, 'Python'), (101, 'SQL'), (101, 'Machine Learning'),
(102, 'Java'), (102, 'Spring Boot'), (103, 'React'), (103, 'JavaScript');
SELECT emp_id, 
       GROUP_CONCAT(skill ORDER BY skill SEPARATOR ', ') AS skills
FROM employee_skills
GROUP BY emp_id;

Output:

Explanation: Here, the group_concat() groups skills by emp_id and concatenates them into one field. The ORDER BY within GROUP_CONCAT() ensures an alphabetical order of skills.

3. Book Store

In a library, books are produced with multiple authors, and such will hopefully be displayed in a single row per book for purposes of easy identification.

Example:

CREATE TABLE book_authors (
    book_id INT,
    book_name VARCHAR(255),
    author VARCHAR(255)
);
INSERT INTO book_authors (book_id, book_name, author) VALUES
(1, 'Database Systems', 'Elmasri'), 
(1, 'Database Systems', 'Navathe'),
(2, 'Clean Code', 'Robert C. Martin'),
(3, 'The Pragmatic Programmer', 'Andrew Hunt'),
(3, 'The Pragmatic Programmer', 'David Thomas');
SELECT book_id, book_name, 
       GROUP_CONCAT(author ORDER BY author SEPARATOR ', ') AS authors
FROM book_authors
GROUP BY book_id, book_name;

Output:

Explanation: Here, the GROUP BY makes sure that each book, as identified by Book_id, is grouped. SEPARATOR ‘,’ ensures that this multiple authorship is separated with a comma and a space. 

Best Practices

• Effective Use of GROUP_CONCAT(): For string aggregation, use GROUP_CONCAT() with the appropriate ORDER BY and SEPARATOR.
• Regulating the String Length: In MySQL, group_concat_max_len typically has a default value of 1024 bytes, though it can be raised if necessary.
• Selecting Different Separators: When values contain commas, use easily distinct separators (such as |,;,, ) to avoid misunderstanding.
• Enhancing Performance: To cut down on superfluous ORDER BY clauses, employ indexing with larger datasets. 

Conclusion

The various concatenation methods for combining several MySQL rows into a single field are strong methods that provide an effective means of presenting data. Depending on the performance requirements, variables, JSON_ARRAYAGG(), and GROUP_CONCAT() each have advantages. GROUP_CONCAT() is the general standard method for a basic row aggregation, whereas JSON_ARRAYAGG() provides structured JSON output. 

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.

How to Concatenate Multiple Rows into One Field in MySQL – FAQs