How can rows be concatenated with comma separators in MySQL?

---

You can use the following syntax in MySQL to concatenate the values from multiple rows with a comma:

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

This particular example selects the team column, then creates a new column named all_points that concatenates all values from the points column with a comma in between each value.

The following example shows how to use this syntax in practice.

Example: How to Concatenate Rows with Comma in MySQL

Suppose we have the following table named athletes that contains information about various basketball players:

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

-- insert rows into table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 14);
INSERT INTO athletes VALUES (0003, 'Spurs', 37);
INSERT INTO athletes VALUES (0004, 'Mavs', 19);
INSERT INTO athletes VALUES (0005, 'Rockets', 26);
INSERT INTO athletes VALUES (0006, 'Rockets', 35);
INSERT INTO athletes VALUES (0007, 'Rockets', 14);

-- view all rows in table
SELECT * FROM athletes;

Output:

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

Suppose that we would like to concatenate together the values from the points column for each unique team.

We can use the following syntax to do so:

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

Output:

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

This query concatenates together all of the points values for each team, using a comma as the separator.

Note #1: We used the AS statement to name the new column all_points.

Note #2: If you would like to use a different separator when concatenating values together, simply specify that separator after the SEPARATOR keyword in the GROUP_CONCAT() function.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL: