? MySQL GROUP_CONCAT()

? Quick Overview

The GROUP_CONCAT() function in MySQL concatenates values from multiple rows into a single string. It's typically used with GROUP BY to aggregate grouped rows into one combined string result.

? Key Concepts

GROUP_CONCAT(): Combines values from multiple rows into one string (default separator is comma).
ORDER BY: Controls order of values inside the concatenated string.
SEPARATOR: Customizes the delimiter between concatenated values.
DISTINCT: Removes duplicate values before concatenation.
GROUP_CONCAT_MAX_LEN: Server variable controlling max length of result (default often 1024).

? Syntax / Theory

? View Code Example

// Syntax for GROUP_CONCAT() with optional parts shown
GROUP_CONCAT([DISTINCT] expression
ORDER BY expression [ASC | DESC]
SEPARATOR 'separator');

? Examples

? View Code Example

// Get a comma-separated list of employee names by department
SELECT department, GROUP_CONCAT(employee_name SEPARATOR ', ') AS employees
FROM employees
GROUP BY department;

? View Code Example

// Get product names in each category, sorted alphabetically
SELECT category, GROUP_CONCAT(product_name ORDER BY product_name ASC SEPARATOR ', ') AS products
FROM products
GROUP BY category;

? View Code Example

// Using DISTINCT to avoid duplicates and a custom separator
SELECT category, GROUP_CONCAT(DISTINCT product_name ORDER BY product_name ASC SEPARATOR ' | ') AS unique_products
FROM products
GROUP BY category;

? View Code Example

// Example: increase GROUP_CONCAT max length (session-level)
SET SESSION group_concat_max_len = 2048;

? Explanation / Live Output

What the queries return

The first query returns one row per department with a single column employees containing names joined by commas. The second groups product names per category in alphabetical order. The DISTINCT example removes duplicate product names before joining and uses ' | ' as separator.

Note: If concatenated results exceed group_concat_max_len, they will be truncated — adjust the variable if needed.

✅ Tips & Best Practices

Use GROUP_CONCAT() to create readable, combined string representations of grouped rows.
Specify ORDER BY inside GROUP_CONCAT() when the order matters.
Use DISTINCT to remove duplicates before concatenation.
Adjust group_concat_max_len at session/server level when expected output is large.

? Try It Yourself / Practice Tasks

Write a query to show all course names taken by each student in one row, separated by commas.
Use GROUP_CONCAT() with ORDER BY to list employees in each department alphabetically.
Experiment with custom separators (e.g., ' | ') and remove duplicates using DISTINCT.