How to Concatenate Rows with a Comma in MySQL Using GROUP_CONCAT()

by

Concatenate Rows with a Comma in MySQL, When working with MySQL databases, you will sometimes need to combine values from multiple rows into a single string.

This is especially useful when generating reports, creating summary tables, preparing data for applications, or displaying multiple related values in a single record.

For example, suppose a basketball database contains several players belonging to the same team. Instead of displaying each player’s points on a separate row, you may want to produce a single row for each team with all of the points separated by commas.

MySQL provides the GROUP_CONCAT() aggregate function for this purpose.

The basic syntax is:

SELECT
    team,
    GROUP_CONCAT(points SEPARATOR ', ') AS all_points
FROM athletes
GROUP BY team;

This query groups records by team and concatenates the corresponding values from the points column into a single string, using a comma and space between values.

Example: How to Concatenate Rows with Comma in MySQL

Suppose we have a table named athletes containing information about basketball players.

We can create the table using:

CREATE TABLE athletes (
    id INT PRIMARY KEY,
    team TEXT NOT NULL,
    points INT NOT NULL
);

Next, insert some sample data:

INSERT INTO athletes VALUES
(1, 'Mavs', 22),
(2, 'Mavs', 14),
(3, 'Spurs', 37),
(4, 'Mavs', 19),
(5, 'Rockets', 26),
(6, 'Rockets', 35),
(7, 'Rockets', 14);

You can view all records with:

SELECT *
FROM athletes;

The result is:

+----+---------+--------+
| id | team    | points |
+----+---------+--------+
|  1 | Mavs    |     22 |
|  2 | Mavs    |     14 |
|  3 | Spurs   |     37 |
|  4 | Mavs    |     19 |
|  5 | Rockets |     26 |
|  6 | Rockets |     35 |
|  7 | Rockets |     14 |
+----+---------+--------+

Concatenate Rows with a Comma

Suppose we want to combine all values from the points column for each unique team.

We can use:

SELECT
    team,
    GROUP_CONCAT(points SEPARATOR ', ') AS all_points
FROM athletes
GROUP BY team;

The output is:

+---------+------------+
| team    | all_points |
+---------+------------+
| Mavs    | 22, 14, 19 |
| Rockets | 26, 35, 14 |
| Spurs   | 37         |
+---------+------------+

The GROUP_CONCAT() function combines the values belonging to each group into a single string.

For the Mavs, the individual values:

22
14
19

become:

22, 14, 19

Similarly, the Rockets values:

26
35
14

become:

26, 35, 14

How GROUP_CONCAT() Works

The general syntax is:

GROUP_CONCAT(expression)

You can optionally specify a separator:

GROUP_CONCAT(expression SEPARATOR 'separator')

For example:

GROUP_CONCAT(points SEPARATOR ', ')

means that MySQL should concatenate the values from points and place , between them.

The GROUP BY clause determines which rows are combined.

In our example:

GROUP BY team

means that all rows belonging to the same team are processed together.

Use AS to Name the New Column

Without an alias, the calculated column may have an expression-based name.

You can make the result easier to understand by using AS:

SELECT
    team,
    GROUP_CONCAT(points SEPARATOR ', ') AS all_points
FROM athletes
GROUP BY team;

Here, all_points is the name of the new column.

You can choose any meaningful alias, such as:

SELECT
    team,
    GROUP_CONCAT(points SEPARATOR ', ') AS player_points
FROM athletes
GROUP BY team;

Use a Different Separator

The SEPARATOR keyword allows you to choose how the values should be separated.

For example, use a semicolon:

SELECT
    team,
    GROUP_CONCAT(points SEPARATOR '; ') AS all_points
FROM athletes
GROUP BY team;

The result could look like:

+---------+------------+
| team    | all_points |
+---------+------------+
| Mavs    | 22; 14; 19 |
| Rockets | 26; 35; 14 |
| Spurs   | 37         |
+---------+------------+

You can use a pipe character:

SELECT
    team,
    GROUP_CONCAT(points SEPARATOR ' | ') AS all_points
FROM athletes
GROUP BY team;

Result:

22 | 14 | 19

You can also use a custom string:

SELECT
    team,
    GROUP_CONCAT(points SEPARATOR ' / ') AS all_points
FROM athletes
GROUP BY team;

Result:

22 / 14 / 19

Concatenate Values with a Comma Without a Space

If you want only a comma between values rather than a comma followed by a space, use:

SELECT
    team,
    GROUP_CONCAT(points SEPARATOR ',') AS all_points
FROM athletes
GROUP BY team;

The result is:

22,14,19

Compare this with:

GROUP_CONCAT(points SEPARATOR ', ')

which produces:

22, 14, 19

The second format is usually easier for humans to read.

Sort Values Inside GROUP_CONCAT()

You can control the order of values using ORDER BY inside GROUP_CONCAT().

For example, to sort points from smallest to largest:

SELECT
    team,
    GROUP_CONCAT(
        points
        ORDER BY points
        SEPARATOR ', '
    ) AS all_points
FROM athletes
GROUP BY team;

The result is:

+---------+------------+
| team    | all_points |
+---------+------------+
| Mavs    | 14, 19, 22 |
| Rockets | 14, 26, 35 |
| Spurs   | 37         |
+---------+------------+

To sort from largest to smallest, use DESC:

SELECT
    team,
    GROUP_CONCAT(
        points
        ORDER BY points DESC
        SEPARATOR ', '
    ) AS all_points
FROM athletes
GROUP BY team;

The result is:

+---------+------------+
| team    | all_points |
+---------+------------+
| Mavs    | 22, 19, 14 |
| Rockets | 35, 26, 14 |
| Spurs   | 37         |
+---------+------------+

This is useful when the order of the concatenated values matters.

Concatenate Distinct Values

You can use DISTINCT inside GROUP_CONCAT() to remove duplicate values.

For example:

SELECT
    team,
    GROUP_CONCAT(
        DISTINCT points
        ORDER BY points
        SEPARATOR ', '
    ) AS unique_points
FROM athletes
GROUP BY team;

Suppose the data contains:

Mavs   22
Mavs   14
Mavs   22
Mavs   19

The result would contain each unique points value only once:

14, 19, 22

This is useful when duplicate values are not meaningful for your analysis.

Concatenate Text Values

GROUP_CONCAT() is not limited to numeric values.

Suppose we add a player column:

CREATE TABLE team_players (
    id INT PRIMARY KEY,
    team VARCHAR(50),
    player VARCHAR(100)
);

Insert some data:

INSERT INTO team_players VALUES
(1, 'Mavs', 'Player A'),
(2, 'Mavs', 'Player B'),
(3, 'Mavs', 'Player C'),
(4, 'Rockets', 'Player D'),
(5, 'Rockets', 'Player E');

You can concatenate player names:

SELECT
    team,
    GROUP_CONCAT(player SEPARATOR ', ') AS players
FROM team_players
GROUP BY team;

The result could be:

+---------+------------------------+
| team    | players                |
+---------+------------------------+
| Mavs    | Player A, Player B, Player C |
| Rockets | Player D, Player E     |
+---------+------------------------+

This is one of the most common practical uses of GROUP_CONCAT().

Concatenate Names in Alphabetical Order

You can combine ORDER BY and SEPARATOR:

SELECT
    team,
    GROUP_CONCAT(
        player
        ORDER BY player
        SEPARATOR ', '
    ) AS players
FROM team_players
GROUP BY team;

This ensures that the player names are alphabetically ordered inside each team’s concatenated string.

Concatenate Multiple Columns

You can also concatenate multiple fields before passing the result to GROUP_CONCAT().

Suppose you have:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    department VARCHAR(50),
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

You could generate a list of full names for each department:

SELECT
    department,
    GROUP_CONCAT(
        CONCAT(first_name, ' ', last_name)
        ORDER BY last_name
        SEPARATOR ', '
    ) AS employees
FROM employees
GROUP BY department;

This produces a single employee list for each department.

Concatenate Values with a Custom Format

GROUP_CONCAT() can be combined with other string functions to create formatted output.

For example:

SELECT
    team,
    GROUP_CONCAT(
        CONCAT('Points: ', points)
        SEPARATOR ', '
    ) AS point_summary
FROM athletes
GROUP BY team;

A result might look like:

+---------+------------------------------+
| team    | point_summary                |
+---------+------------------------------+
| Mavs    | Points: 22, Points: 14, Points: 19 |
| Rockets | Points: 26, Points: 35, Points: 14 |
| Spurs   | Points: 37                    |
+---------+------------------------------+

This technique is useful for generating human-readable summaries directly from SQL.

GROUP_CONCAT() and NULL Values

GROUP_CONCAT() ignores NULL values.

For example, suppose the data contains:

Mavs    22
Mavs    NULL
Mavs    19

The result would be:

22, 19

The NULL value is not included in the concatenated result.

This behavior can be useful, but you should understand it when working with incomplete data.

GROUP_CONCAT() with a WHERE Clause

You can filter rows before concatenating them.

For example, suppose you only want to include players who scored at least 20 points:

SELECT
    team,
    GROUP_CONCAT(points SEPARATOR ', ') AS high_scores
FROM athletes
WHERE points >= 20
GROUP BY team;

The result is:

+---------+------------+
| team    | high_scores|
+---------+------------+
| Mavs    | 22         |
| Rockets | 26, 35     |
| Spurs   | 37         |
+---------+------------+

The WHERE clause filters the rows before GROUP_CONCAT() performs the aggregation.

GROUP_CONCAT() with ORDER BY and DISTINCT

For more advanced queries, you can combine several features:

SELECT
    team,
    GROUP_CONCAT(
        DISTINCT points
        ORDER BY points DESC
        SEPARATOR ', '
    ) AS unique_points
FROM athletes
GROUP BY team;

This query:

  1. Groups rows by team.
  2. Removes duplicate points.
  3. Sorts the remaining values from highest to lowest.
  4. Combines them into one string.
  5. Separates values with a comma and space.

This is a useful pattern for creating compact summaries.

Important: GROUP_CONCAT() Has a Maximum Length

One important consideration when using GROUP_CONCAT() is that MySQL limits the maximum length of the resulting string.

The relevant system variable is:

group_concat_max_len

You can inspect its current value with:

SHOW VARIABLES LIKE 'group_concat_max_len';

If you need a larger result, you can increase the session value:

SET SESSION group_concat_max_len = 100000;

For very large groups, this is important because the result can otherwise be truncated.

The appropriate value depends on the size of the data and your application’s requirements.

GROUP_CONCAT() for Reporting

GROUP_CONCAT() is particularly useful in reporting queries.

For example, suppose an e-commerce database contains customers and their orders. Instead of returning one row per order, you could create a summary containing all order IDs for each customer:

SELECT
    customer_id,
    GROUP_CONCAT(order_id ORDER BY order_id SEPARATOR ', ') AS order_ids
FROM orders
GROUP BY customer_id;

A result might look like:

+-------------+----------------+
| customer_id | order_ids      |
+-------------+----------------+
| 101         | 1001, 1004, 1012 |
| 102         | 1002, 1007      |
| 103         | 1003            |
+-------------+----------------+

This can be useful when building administrative dashboards and summary reports.

Practical Business Use Cases

There are many situations where concatenating rows into a comma-separated string can be useful.

Customer Orders

Create a list of order IDs for each customer:

SELECT
    customer_id,
    GROUP_CONCAT(order_id ORDER BY order_id SEPARATOR ', ') AS orders
FROM orders
GROUP BY customer_id;

Product Categories

Create a list of categories associated with each product:

SELECT
    product_id,
    GROUP_CONCAT(category_name ORDER BY category_name SEPARATOR ', ') AS categories
FROM product_categories
GROUP BY product_id;

Employee Skills

Create a comma-separated list of skills for each employee:

SELECT
    employee_id,
    GROUP_CONCAT(skill_name ORDER BY skill_name SEPARATOR ', ') AS skills
FROM employee_skills
GROUP BY employee_id;

Tags and Metadata

You can create a single list of tags for each article:

SELECT
    article_id,
    GROUP_CONCAT(tag_name ORDER BY tag_name SEPARATOR ', ') AS tags
FROM article_tags
GROUP BY article_id;

This can be particularly useful when preparing data for reporting or exporting.

Data Analytics

Analytics teams can use GROUP_CONCAT() to create compact summaries of categorical values associated with a group.

For example:

SELECT
    customer_segment,
    GROUP_CONCAT(DISTINCT product_category SEPARATOR ', ') AS categories
FROM purchases
GROUP BY customer_segment;

GROUP_CONCAT() vs CONCAT()

Although their names are similar, GROUP_CONCAT() and CONCAT() perform different tasks.

CONCAT() combines values from columns or expressions within the same row.

For example:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

GROUP_CONCAT() combines values from multiple rows within a group.

For example:

SELECT
    department,
    GROUP_CONCAT(employee_name SEPARATOR ', ') AS employees
FROM employees
GROUP BY department;

A simple way to remember the difference is:

CONCAT()       → combines values within a row
GROUP_CONCAT() → combines values across rows

Complete MySQL Example

Here is a complete example that you can copy and run:

CREATE TABLE athletes (
    id INT PRIMARY KEY,
    team VARCHAR(50) NOT NULL,
    points INT NOT NULL
);

INSERT INTO athletes VALUES
(1, 'Mavs', 22),
(2, 'Mavs', 14),
(3, 'Spurs', 37),
(4, 'Mavs', 19),
(5, 'Rockets', 26),
(6, 'Rockets', 35),
(7, 'Rockets', 14);

SELECT
    team,
    GROUP_CONCAT(points SEPARATOR ', ') AS all_points
FROM athletes
GROUP BY team;

Expected result:

+---------+------------+
| team    | all_points |
+---------+------------+
| Mavs    | 22, 14, 19 |
| Rockets | 26, 35, 14 |
| Spurs   | 37         |
+---------+------------+

You can also sort the values:

SELECT
    team,
    GROUP_CONCAT(
        points
        ORDER BY points DESC
        SEPARATOR ', '
    ) AS all_points
FROM athletes
GROUP BY team;

This produces:

+---------+------------+
| team    | all_points |
+---------+------------+
| Mavs    | 22, 19, 14 |
| Rockets | 35, 26, 14 |
| Spurs   | 37         |
+---------+------------+

Frequently Asked Questions

How do I concatenate rows with a comma in MySQL?

Use GROUP_CONCAT() with the SEPARATOR option:

SELECT
    team,
    GROUP_CONCAT(points SEPARATOR ', ') AS all_points
FROM athletes
GROUP BY team;

How do I concatenate rows without a space after the comma?

Use:

GROUP_CONCAT(points SEPARATOR ',')

instead of:

GROUP_CONCAT(points SEPARATOR ', ')

How do I remove duplicate values from GROUP_CONCAT()?

Use DISTINCT:

GROUP_CONCAT(DISTINCT points SEPARATOR ', ')

How do I sort values inside GROUP_CONCAT()?

Use ORDER BY:

GROUP_CONCAT(
    points
    ORDER BY points DESC
    SEPARATOR ', '
)

Can GROUP_CONCAT() concatenate text values?

Yes. For example:

SELECT
    team,
    GROUP_CONCAT(player_name SEPARATOR ', ') AS players
FROM athletes
GROUP BY team;

What happens to NULL values?

GROUP_CONCAT() ignores NULL values.

Can I use GROUP_CONCAT() with WHERE?

Yes. The WHERE clause filters rows before the values are aggregated:

SELECT
    team,
    GROUP_CONCAT(points SEPARATOR ', ') AS all_points
FROM athletes
WHERE points >= 20
GROUP BY team;

What is the difference between CONCAT() and GROUP_CONCAT()?

CONCAT() combines values from expressions or columns within a row, while GROUP_CONCAT() combines values from multiple rows into a single string for each group.

Conclusion

MySQL’s GROUP_CONCAT() function provides a convenient way to combine values from multiple rows into a single comma-separated string.

The basic syntax is:

GROUP_CONCAT(column_name SEPARATOR ', ')

When combined with GROUP BY, it allows you to create one summarized row for each group:

SELECT
    team,
    GROUP_CONCAT(points SEPARATOR ', ') AS all_points
FROM athletes
GROUP BY team;

You can customize the result with DISTINCT, ORDER BY, and different separators:

GROUP_CONCAT(
    DISTINCT points
    ORDER BY points DESC
    SEPARATOR ', '
)

This makes GROUP_CONCAT() useful for customer order summaries, employee skills, product categories, tags, reporting dashboards, analytics, and many other MySQL applications.

One important consideration is the group_concat_max_len setting, particularly when concatenating large numbers of values. For large datasets, you should also consider whether storing many values in a single comma-separated string is appropriate for your application’s data model. In many cases, normalized relational tables remain the better choice for storage, while GROUP_CONCAT() is best used to produce a formatted result for reporting or presentation.

Tags:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

1 × five =