Advanced Example of GROUP_CONCAT Function

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:

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:

Explanation: We get the desired output from the above query.

Now we will provide a custom separator in the second argument to concatenate the languages.

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

Output:

Explanation: In the above query, we retrieves data from the language table and groups it by the state column. For each group of states it concatenates the name column values using the GROUP_CONCAT function through storing the result in a column named different_languages.

We can even make use of subquery and ORDER BY clause to order the different languages in ascending order before concatenation.

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

Output:

Explanation: In the above query, we first selects the state and name columns from the language table, orders the results by the name column, and then groups the data by the state column. For each group of states, it concatenates the name values into a single string using the GROUP_CONCAT function and the result is stored in a column named different_languages.

In SQLite databases, string concatenation is crucial for various tasks, from generating reports to organizing data. One particularly useful function for this purpose is GROUP_CONCAT. SQLite is a lightweight and versatile relational database management system that offers a powerful solution with its GROUP_CONCAT function. In this article, we’ll explore how to use GROUP_CONCAT to concatenate strings effortlessly within SQLite queries.