MySQL GROUP_CONCAT
Overview
GROUP_CONCAT is a handy function in MYSQL that allows us to concatenate strings from multiple rows into a single string. It is a great way to avoid redundancy and combine data from multiple rows and display it in a very readable format.
Syntax of GROUP_CONCAT in MySQL
In the above syntax, Col1, Col2, and ColN are the column names of the table. TableName is the table's name and ColumnX is the column chosen by the user among all the other columns.
I agree, things might be getting confusing for you looking at all these words and brackets, but it just looks intimidating, there's nothing much to it.
Let's learn the meaning of this syntax in the next section where we will explain all its parameters.
Parameters of GROUP_CONCAT in MySQL
The GROUP_CONCAT function in MySQL has a lot of parameters, let us have a look at them one by one.
- DISTINCT (Optional): This keyword specifies that duplicate values in the concatenated string should be removed.
- Expression: This is the column name or expression which we want to concatenate.
- ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [, col_name ...] (Optional): This parameter is used to specify the order of the values inside the concatenated string.
- SEPARATOR sep_string (Optional): This parameter is used to specify a separator string (sep_string) between the concatenated values. The separator is a comma (',') by default unless specified.
Return Value of GROUP_CONCAT in MySQL
The GROUP_CONCAT() function in MySQL returns a single concatenated string that is derived from the specified columns or expressions. The resulting string contains all the values from the specified columns or expressions, concatenated together with the specified/default separator.
Imagine you have a table with a column named cars and the values in the column are "Toyota", "Maybach", and "Mitsubishi". Now let's say you want to concatenate these values together in a single string, for which we will use the GROUP_CONCAT() function that returns the concatenated string as "Toyota, Maybachh, Mitsubishi".
How does the GROUP_CONCAT in MySQL Work?
Think of your MySQL database as a pantry full of delicious ingredients, and your GROUP_CONCAT() functions like a recipe for a tasty dish. Just like a recipe combines multiple ingredients and the outcome is a delicious meal, the GROUP_CONCAT() function combines multiple rows in your table to output a concatenated string.
To start the recipe, we need to choose the ingredients. In MySQL, we need to choose the column or expression we want to concatenate and then we call the GROUP_CONCAT() function using it.
Next, we can add some varieties to the recipe. Like a DISTINCT keyword that ensures there aren't any duplicate values in the final output, like how you would weed out any extra, unwanted, or rotten vegetables from your recipe.
To spice things up, we can use the ORDER BY clause to sort the values within the concatenated string, just like adding a pinch of your favorite spice to your recipe.
Finally, we will add some sauce to the recipe by including a SEPARATOR parameter to specify the string that should be used to separate the values inside the concatenated string. This will bring everything together like a drizzle of your favorite sauce does to your meal.
Once all the ingredients are mixed, the GROUP_CONCAT function returns the resulting concatenated string as the output, just like how you would end up with your delicious meal after following a recipe.
Examples of GROUP_CONCAT Function
Concatenating All Values in a Column from Different Rows
Let's say we have a table named "Fruits" with columns "Id" and "Name" that contains the names of different fruits:
Fruits
| Id | Name |
|---|---|
| 1 | Mango |
| 2 | Watermelon |
| 3 | Pineapple |
| 4 | Valencia Orange |
To concatenate all the fruit names into a single string, the query is as follows:
Query
Output
| AllFruits |
|---|
| Mango, Watermelon, Pineapple, Valencia Orange |
Explanation
The GROUP_CONCAT() function returns a concatenated string of all the Fruit Name strings. The result is a single row with a single column "AllFruits" that contains the concatenated string of all the fruit names in the Fruits table.
Concatenating Multiple Values Using a Custom Separator
Let's say we have a table called "Cars" with columns named "Id", "CustomerName", and "CarName":
Cars
| Id | CustomerName | CarName |
|---|---|---|
| 1 | Ayush | Lexus |
| 2 | Josh | Carnival |
| 3 | Ayush | Hycross |
| 4 | Batman | Batmobile |
To concatenate the distinct cars for each customer and separate them with a semicolon, the query is as follows:
Query
Output
| CustomerName | Products |
|---|---|
| Batman | Batmobile |
| Josh | Carnival |
| Ayush | Lexus, Hycross |
Explanation
A table with two columns (CustomerName and Products) will be returned, where the Products column contains the concatenated string in which the concatenated values are separated with the help of a custom separator ("; ").
Concatenating Multiple Values and Sorting the Resultant String
Let's say we have a table called "ToDo" with columns named "Id" and "Task":
ToDo
| Id | Task |
|---|---|
| 1 | Study |
| 2 | Go for a walk |
| 3 | Write articles |
| 4 | Pay bills |
To concatenate all the tasks in descending order into a single string, the query is as follows:
Query
Output
| TaskList |
|---|
| Study, Go for a walk, Write articles, Pay bills |
Explanation
The GROUP_CONCAT() function returns a concatenated string of all the Task strings in descending order using the ORDER BY clause. The result is a single row with a single column "TaskList" that contains the concatenated string of all the tasks in descending order in the Fruits table.
Concatenating Values in a Column with a Limit and a Custom Separator
Let's say we have a table called "Movies" with columns named "Id" and "Name":
Movies
| Id | Name |
|---|---|
| 1 | Revenant |
| 2 | Shutter Island |
| 3 | The Dark Knight |
| 4 | Tumbbad |
| 5 | Interstellar |
| 6 | Inception |
| 7 | Tenet |
| 8 | Raman Raghav 2.0 |
| 9 | Madari |
| 10 | Ugly |
To concatenate all the tasks in descending order into a single string, the query is as follows:
Query
Output
| MovieList |
|---|
| Revenant ~ Shutter Island ~ The Dark Knight ~ Tumbbad ~ Interstellar ~ Inception |
Explanation
In this example, we are limiting the values to 6 in the concatenated string using the LIMIT clause and ORDER BY clause, using them together, we get the first 6 movie names in the concatenated string. We have also used a custom separator (" ~ ") to separate the values in the concatenated string.
Concatenating With Null Handling
Let's say we have a table called "Students" with columns named "StudentId", "FullName", and "MajorSubject":
Students
| StudentId | FullName | MajorSubject |
|---|---|---|
| 1 | Ayush Kumar | Physics |
| 2 | John Smith | Chemistry |
| 3 | Chris Evans | Computer Science |
| 4 | Levi Ackerman | Physical Education |
| 5 | Bruce Lee | NULL |
| 6 | Mark Ruffalo | NULL |
| 7 | Armin | NULL |
| 8 | Ana De Armas | Spanish |
| 9 | John Wick | Martial Arts |
| 10 | Slim Shady | Singing |
To concatenate all the MajorSubjects into a single string with NULL handling, the query is as follows:
Query
Output
| AllSubjects |
|---|
| Physics, Chemistry, Computer Science, Physical Education, To be assigned, To be assigned, To be assigned, Spanish, Martial Arts, Singing |
Explanation
In this example, we are handling the null values in the "MajorSubject" column by assigning those values to "To be assigned". Hence, the final output is a concatenated string of all the Major Subjects with "To be assigned" in place of null values.
Conclusion
- The GROUP_CONCAT() is an aggregate function in MYSQL that concatenate strings from multiple rows of a single column into a single string. It is often used to aggregate data from related rows in a table.
- By default, the GROUP_CONCAT() function includes all values for a given column. We can use the DISTINCT keyword to avoid duplicate deals in the concatenated string.
- We can use the ORDER BY clause in the GROUP_CONCAT() function to sort the concatenated values. The values can be sorted by a column name or an expression in either ascending (ASC) or descending (DESC) order.
- We can also specify the separator between the concatenated values by using the SEPARATOR keyword. By default, the separator is a comma (', '), but we can use any custom separator we want.
- The length of the concatenated values returned by the GROUP_CONCAT() function can be limited by using the LIMIT keyword in the said function.
See Also
To move further in MySQL, you can learn these topics:
- LEAD() and LAG() function in MySQL
- PARTITION BY clause in MySQL
- Ranking functions in MySQL
- MySQL SUBSTRING() function
- UPPER() and LOWER() function in MySQL