Can I concatenate multiple MySQL rows into one field?

sql

group-concat

distinct

data-aggregation

byAnton Shumikhin·Aug 9, 2024

In MySQL, you can leverage the GROUP_CONCAT function to merge values from multiple rows into one field. Typically, this function creates a list of values for each group that's specified in the GROUP BY clause.

Here's a practical example that concatenates name entries with unique group_id's:

-- This SQL snippet finds all unique names and invites them to a form of group gathering (say, a party!)
SELECT group_id, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS combined_names
FROM users
GROUP BY group_id;

With this script, all names are gathered into a combined_names string, sorted alphabetically, for each distinct group_id.

Settings for string length

You might encounter a tricky scenario with GROUP_CONCAT where the resulting string exceeds the default length limit. To avoid this, you can increase the string length limit at the beginning of your session with group_concat_max_len.

-- I don't always concatenate strings, but when I do, I make them longer.
SET SESSION group_concat_max_len = 1024;

Removing duplicates with DISTINCT

The DISTINCT keyword within GROUP_CONCAT removes those pesky duplicate values from your concatenated results – very handy when joining multiple tables with related data.

-- Same name? No problem! I got DISTINCT.
SELECT group_id, GROUP_CONCAT(DISTINCT name ORDER BY name SEPARATOR ', ') AS combined_names
FROM users
JOIN orders ON users.user_id = orders.user_id
GROUP BY group_id;

Using conditionals within concatenation

GROUP_CONCAT can play nicely with conditional statements like CASE or IF. This allows you to conditionally concatenate strings based on a certain criterion.

-- In the world of concatenated strings, age does matter.
SELECT group_id, 
       GROUP_CONCAT(CASE WHEN age > 18 THEN name END SEPARATOR ', ') AS adult_names
FROM users
GROUP BY group_id;

Dynamic settings for vast data

Handling large datasets with grace is an art. Dynamically setting the group_concat_max_len can combat unexpected truncation issues. Calculate the necessary length prior to your query and set it accordingly.

-- DJ Khaled: Another one...Another one...Another big data in concatenated string.
SET @max_length := (SELECT SUM(CHAR_LENGTH(name)) + COUNT(*) * CHAR_LENGTH(SEPARATOR)
                    FROM users);
SET SESSION group_concat_max_len = @max_length;

Juggling numbers and dates

Remember, numerical values and dates are not strangers to GROUP_CONCAT. By pairing this function with the likes of SUM(), you can navigate through complex aggregations.

-- Don't be shy! Show me the money... in CONCAT form!
SELECT group_id, 
       GROUP_CONCAT(CONCAT(name, ': $', CAST(SUM(sales) AS CHAR)) SEPARATOR ', ') AS name_sales
FROM users
JOIN sales USING (user_id)
GROUP BY group_id;

Wrestling with one-to-many relations

In the grand arena of one-to-many relationships, GROUP_CONCAT emerges as a champ, transforming these datasets into compact, digestible strings.

-- One product, many categories. Let's sort this out.
SELECT p.product_name, 
       GROUP_CONCAT(DISTINCT c.category_name ORDER BY c.category_name SEPARATOR ', ') AS categories
FROM products p
JOIN product_categories pc ON p.id = pc.product_id
JOIN categories c ON pc.category_id = c.id
GROUP BY p.product_name;