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

Try Hands-On: Fiddle

Create Table: Gist

Desired Output

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.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to efficiently convert rows to columns in SQL?
2. How to transpose columns to rows in SQL?
3. How to select first row in each GROUP BY group?