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.