How to concatenate multiple rows into one field in MySQL?
Let's see how to concatenate multiple rows into one field in MySQL?
Problem
You have a table with multiple rows of data for each unique identifier and you want to concatenate the values of these rows into a single field for each identifier.
Input
| user_id | interest |
|---|---|
| 1 | Reading |
| 1 | Writing |
| 2 | Painting |
| 2 | Singing |
| 3 | Traveling |
Try Hands-On: Fiddle
Create Table: Gist
Desired Output
| user_id | concatenated_interests |
|---|---|
| 1 | Reading,Writing |
| 2 | Painting,Singing |
| 3 | Traveling |
Solution 1:
Using Group_Concat and Group By
SELECT user_id,
GROUP_CONCAT(interest ORDER BY interest ASC) AS concatenated_interests
FROM user_interests
GROUP BY user_id;Explanation:
The GROUP_CONCAT() function concatenates values from multiple rows into a single string.
If you want the concatenated string to be in a specific order, you can use the ORDER BY clause within the GROUP_CONCAT() function as well.
We are grouping by the user_id column to ensure that we get a single row for each useommas.
Solution 2:
Using Variables Inside a Derived Table
SET @prev_user_id = NULL, @concat_interests = '';
SELECT
user_id,
MAX(concatenated_interests) AS concatenated_interests
FROM (
SELECT
user_id,
(CASE
WHEN @prev_user_id = user_id THEN @concat_interests := CONCAT(@concat_interests, ',', interest)
ELSE @concat_interests := interest
END) AS concatenated_interests,
@prev_user_id := user_id
FROM
user_interests
ORDER BY
user_id, interest
) AS derived_table
GROUP BY
user_id;Explanation:
We initialize two session variables, @prev_user_id and @concat_interests, to keep track of the current user we’re processing and the accumulated interests for that user.
The derived table (subquery) processes the user_interests table row-by-row in the order of user_id and interest. As it processes each row:
If the user_id matches @prev_user_id, it appends the interest to @concat_interests.
If the user_id is different, it resets @concat_interests to the current row’s interest.
The outer query then groups by user_id and uses the MAX() function to get the fully concatenated string for each user.