SQLite group_concat

Definition on SQLite group_concat Function

SQLite provides different types of functions to the user; the group_concat function is one of the SQLite functions. With the help of the SQLite group_concat function, we can combine more than one result into a single column as per user requirement. SQLite group_concat function is also called a string aggregation function. SQLite group_concat only works on the non-null values in the column that are useful for concatenation. SQLite group_concat () function basically works the same as SQLite aggregate functions because the group_concat function works on the set of rows and it returns results as single similarly like aggregate functions.

Syntax:

group_concat (specified expression [separator])

Explanation

In the above syntax, we use group_concat () functions with two parameters as follows.

Expression: expression is a column name or any expression that we need to concatenate.

Separator: This is used to separate all values. If we skip the separator then the group_concat () function uses a comma by default.

How group_concat function work in SQLite?

Now let’s see how the group_concat () function works in SQLite as follows.

SQLite group_concat (A) group_concat (A, B) The group_concat() work returns a string which is the connection of all non-NULL estimations of A. In the event that boundary B is available, it is utilized as the separator between cases of A.

SQLite group_concat aggregate function that concatenates the more than one value

Into a single value and a comma is used to separate them. It has some key points as follows.

• SQLite group_concat (A): This will concatenate all the values of A into one string, with the comma “,” utilized as a separator between the values. An invalid quality that means null values will be overlooked.
• SQLite group_concat (A, B): This will concatenate the value of A into one string, with the value of B utilized as a separator between each value rather than the default separator ‘,’. Invalid value likewise will be disregarded.
• SQLite group_concat (DISTINCT A): This will connect every one of the particular values of A into one string, with the comma “,” utilized as a separator between the values. Invalid values will be overlooked.

Examples

Now let’s see the different examples of SQLite group_concat () function as follows.

First, we need to create a table, so create a table by using the following statement as follows.

create table stud (stud_id integer primary key, stud_name text not null, stud_email text not null, stud_mark float not null);

Explanation

In the above example, we created a new table name as stud different attribute such as stud_id with integer data type and primary key, stud_name with text data type and not null, stud_email with text data type and not null, and stud_mark with float data type and not null constraint. The end output of the above statement is shown in the below screenshot as follows.

.table

Now insert some records into the stud table by using insert into a statement as follows.

insert into stud (stud_id, stud_name, stud_email, stud_mark) values (1, “Jay”, “[email protected]”, 90), (2, “Johan”, “[email protected]”, 85), (3, “Jenny”,”[email protected]”, 70), (4, “Sameer”, “[email protected]”, 63), (5, “Pooja”, “[email protected]”, 50);
select * from stud;

Explanation

With the help of the above statement, we inserted some records into the stud table successfully. End output of above statement is shown in the below screenshot as follows.

Now we can use the group_concat () function as follows.

select group_concat (stud_name) from stud;

Explanation

In the above example, we use a select statement with group_concat () function as shown, suppose the user needs to make a group of student names at that time we can use the above statement. Here we just use the group_concat function with a column name that is stud_name. The end output of the above statement is shown in the below screenshot as follows.

Suppose we don’t want to show the comma as a separator in our end result as follows.

select group_concat (stud_name, “;”) from stud;

Explanation

In the above example, we use a select statement with group_concat () function as shown, suppose the user needs to make a group of student names at that time we can use the above statement. If we don’t want to print comma as separators then we can use semicolons instead of a comma. End output of the above statement is shown in the below screenshot as follows.

Let’s see another example, suppose we don’t want to print either comma or semicolon.

select group_concat (stud_name, “|”) from stud;

Explanation

In the above example we use select statement with group_concat () function as shown, suppose user need to make a group of student name at that time we can use the above statement but we don’t want to print comma and semicolon as separator then we can use pipe symbol instead of comma and semicolon. End output of the above statement is shown in the below screenshot as follows.

Now let’s see how we can use group_concat to join as follows.

Now create one more table dept by using the following statement as follows.

create table dept (dept_id integer not null, stud_id integer, class text not null);

Now insert some records into the dept table by using insert into the statement as follows.

insert into dept (dept_id, stud_id, class) values (2, 1, “SE”), (1, 2, “TE”), (3, 4, “BE”), (2, 4, “SE”);
select * from dept;

Explanation

We first created a new table name as dept and we successfully inserted some records. End output of the above statement is shown in the below screenshot as follows.

select group_concat(d.class) from stud as s inner join dept as d on s.stud_id = d.stud_id;

Explanation

Suppose we need to combine data from two different tables and concatenate all class names from dept with respective stud_id. Result separated by using a comma. The end output of the above statement is shown in the below screenshot as follows.

Select group_concat(distinct s.stud_id) from stud as s inner join dept as d on s.stud_id = d.stud_id;

Explanation

See in the above example we use distinct keywords to find out distinct stud_id. The end output of the above statement is shown the below screenshot as follows.

Conclusion

We hope from this article you have understood about the SQLite group_concat function. From the above article, we have learned the basic syntax group_concat function and we also see different examples of the group_concat function. We also learned the rules of case statements. From this article, we learned how and when we use the group_concat function.

Recommended Articles

This is a guide to SQLite group_concat. Here we discuss the Introduction, syntax, How the group_concat function work in SQLite? and examples. You may also have a look at the following articles to learn more –

1. SQL Formatter
2. SQL pivot
3. SQL DESCRIBE TABLE
4. SQL BLOB