GROUP_CONCAT Function

The GROUP_CONCAT is a aggregation function that is used to concat values from various records into one single value. As it is a aggregating function so it is used with GROUP BY clause.

Syntax:

SELECT col1, col2, ..., coln
GROUP_CONCAT ( [DISTINCT] col_name 
[ORDER BY clause]  [SEPARATOR str_val] ) 
FROM table GROUP BY col1, col2, ..., coln;

Explanation:

• col1, col2, …, coln: The columns which will be used to group the records.
• col_name: The name of the column whose value needs to be concatenated.
• clause: The optional clause which can be used to order the data before concatenation.
• str_val: The optional separator value.
• table: The table from which to aggregate the results.

Example 1: Group According to Values

The following query groups the table according to the values in the field ‘val1’ and then uses GROUP_CONCAT() to concat the values of ‘val2‘:

SELECT val1, GROUP_CONCAT(val2) as val2
FROM test
GROUP BY val1;

Output:

Example 2: Group According to Values in Ascending Order

The following query is similar to the query performed in the above examples. The only difference is that it orders the values in ‘val2’ in ascending order by making use of the optional ORDER BY clause before concatenating:

SELECT val1, GROUP_CONCAT(val2 ORDER BY CAST(val2 AS INT)) as val2
FROM test
GROUP BY val1;

Output:

Example 3: Group the Data and Concatenates Values

Like the query presented in example 1, The following query groups the data by ‘val1’ and concatenates values of ‘val2’. The only difference it is that it uses ‘/’ as the separator.

SELECT val1, GROUP_CONCAT(val2 SEPARATOR '/') as val2
FROM test
GROUP BY val1;

Output:

In database management, it is necessary to aggregate and concatenate strings from multiple rows into a single, comma-separated string. This is where the GROUP_CONCAT function in MariaDB comes in to solve these problems. It allows for efficient string concatenation within grouped data and provides a powerful tool for data manipulation. In this article, we will learn about How to use the GROUP_CONCAT function to concatenate strings in MariaDB along with syntax and the various examples and so on.