Languages
[Edit]
EN

MySQL - concatenate multiple rows into one field

0 points
Created by:
Brett4
285

In this article, we would like to show you how to concatenate multiple rows into one field in MySQL.

Quick solution:

SELECT `column1`,
   GROUP_CONCAT(`column2` SEPARATOR 'separator') AS 'alias_name'
FROM `table_name`
GROUP BY `column1`;

Practical example

To show how to combine multiple rows into one field, we will use the following table:

MySQL - example data used to concatenate rows into one field
MySQL - example data used to concatenate rows into one field

Note:

At the end of this article you can find database preparation SQL queries.

Example

In this example, we want to display all the colors and a list of people who like each color in one field.

Query:

SELECT `favorite_color`,
   GROUP_CONCAT(`name` SEPARATOR ', ') AS 'people'
FROM `users`
GROUP BY `favorite_color`;

Output:

MySQL - concatenate multiple rows into one field - result
MySQL - concatenate multiple rows into one field - result

Note:

You can add DISTINCT clause to avoid duplicated names:

SELECT `favorite_color`,
   GROUP_CONCAT(DISTINCT `name` SEPARATOR ', ') AS 'people'
FROM `users`
GROUP BY `favorite_color`;

Database preparation

create_tables.sql file:

CREATE TABLE `users` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	`favorite_color` VARCHAR(100) NOT NULL,
	PRIMARY KEY (`id`)
);

insert_data.sql file:

INSERT INTO `users`
	( `name`, `favorite_color`)
VALUES
	('Tom', 'red'),
	('Chris', 'green'),
	('Kate', 'blue'),
	('Jack', 'green'),
	('Mark', 'green'),
	('Ann', 'orange'),
	('Natalie', 'pink');

MySQL - Problems

MySQL - concatenate multiple rows into one field
Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join