MariaDB GROUP_CONCAT Function to Concatenate Strings

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.

Setting Up Environment

Let’s start by creating the table and inserting some sample values in the table. The following code creates the test table and inserts some entries into it.

CREATE TABLE test (
  id INTEGER PRIMARY KEY,
  val1 VARCHAR(20),
  val2 VARCHAR(20)
);

INSERT INTO test VALUES (21, 'val1', '32');
INSERT INTO test VALUES (11, 'val2', '90');
INSERT INTO test VALUES (90, 'val1', '18');
INSERT INTO test VALUES (77, 'val1', '65');
INSERT INTO test VALUES (43, 'val3', '20');
INSERT INTO test VALUES (81, 'val3', '88');
INSERT INTO test VALUES (29, 'val2', '72');
INSERT INTO test VALUES (55, 'val2', '47');
INSERT INTO test VALUES (72, 'val3', '11');

Output:

Output

Explanation: Now that we have the table in place, lets go through the GROUP_CONCAT() function.

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:

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:

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:

Output

Advanced Example

Let’s understand through the technical example. Let’s create the table and insert some data inside it.

CREATE TABLE language (
  state VARCHAR(50),
  name VARCHAR(50)
);

INSERT INTO language VALUES ('Bihar', 'Hindi');
INSERT INTO language VALUES ('Punjab', 'Punjabi');
INSERT INTO language VALUES ('Assam', 'Assamese');
INSERT INTO language VALUES ('Punjab', 'Urdu');
INSERT INTO language VALUES ('Bihar', 'English');
INSERT INTO language VALUES ('Bihar', 'Maithili');
INSERT INTO language VALUES ('Punjab', 'Hindi');
INSERT INTO language VALUES ('Assam', 'Bengali');
INSERT INTO language VALUES ('Maharashtra', 'Marathi');
INSERT INTO language VALUES ('Gujarat', 'Gujarati');
INSERT INTO language VALUES ('Maharashtra', 'Hindi');
INSERT INTO language VALUES ('Maharashtra', 'English');
INSERT INTO language VALUES ('Assam', 'Bodo');

Output:

Output

Explanation: As we can see in the image, the above table contains the various languages spoken in various states of India.

Let’s use GROUP_CONCAT() function to concat the different languages spoken in a particular state. First we will start by the vanilla version of the query to use GROUP_CONCAT. We will group using the state column and concat the values in the name column.

SELECT state, GROUP_CONCAT(name) different_languages FROM language
GROUP BY state;

Output:

Output

Now we will make use of ORDER BY clause to order the different languages in ascending order.

SELECT state, GROUP_CONCAT(name ORDER BY name) different_languages FROM language
GROUP BY state;

Output:

Output

We can even use the SEPARATOR keyword to define a custom separator to concat the different languages.

SELECT state, GROUP_CONCAT(name SEPARATOR ';') different_languages FROM language
GROUP BY state;

Output:

Output

Conclusion

In this article, we covered how we can make use of GROUP_CONCAT function to concatenate strings in MariaDB. We started by looking at what GROUP_CONCAT is, and then we looked at several examples. In the examples, we made use of the vanilla version, ORDER BY clause and even the SEPARATOR keyword. Finally, we also saw how we can use the concepts we learned in this article to a real-life situation through the technical example.