w3resource

SQLite group_concat() function

Description

The group_concat() function returns a string with concatenated non-NULL value from a group.

Syntax:

group_concat(X)
group_concat(X, Y)

The group_concat() function returns a string which is the concatenation of all non-NULL values of X. If parameter Y is present then it is used as the separator between instances of X. A comma (",") is used as the separator if Y is omitted. The order of the concatenated elements is arbitrary.

Example: SQLite group_concat() function

The following SQLite statement will return a list of a comma(,) separated 'cate_id's for each group of 'pub_id' from the book_mast table.

Sample table: book_mast


Sample Output:

sqlite> SELECT pub_id,group_concat(cate_id)
   ...> FROM book_mast
   ...> GROUP BY pub_id;
pub_id      group_concat(cate_id)
----------  ---------------------
P001        CA002,CA004
P002        CA003,CA003
P003        CA001,CA003
P004        CA002,CA005
P005        CA001,CA004
P006        CA001,CA005
P007        CA005,CA002
P008        CA004,CA005

Pictorial Presentation

SQLite group concat() pictorial presentation

Example: SQLite group_concat() with order by

The following SQLite statement will return unique “cate_id”s , as a list of strings separated by the commas, in ascending order for each group of 'pub_id' from the book_mast table. The order can be changed in descending, using 'DESC' instead of 'ASC' at the end of the select statement.

Sample table: book_mast


Sample Output:

sqlite> SELECT pub_id,group_concat(DISTINCT cate_id)
   ...> FROM book_mast
   ...> GROUP BY pub_id
   ...> ORDER BY group_concat(DISTINCT cate_id) ASC;
pub_id      group_concat(DISTINCT cate_id)
----------  ------------------------------
P003        CA001,CA003
P005        CA001,CA004
P006        CA001,CA005
P001        CA002,CA004
P004        CA002,CA005
P002        CA003
P008        CA004,CA005
P007        CA005,CA002

Example: SQLite group_concat() with distinct

The following SQLite statement will return the unique “cate_id”s, as a list of strings separated by the commas, for each group of 'pub_id' from the book_mast table.

Sample table: book_mast


Sample Output:

sqlite> SELECT pub_id,group_concat(DISTINCT cate_id)
   ...> FROM book_mast
   ...> GROUP BY pub_id;
pub_id      group_concat(DISTINCT cate_id)
----------  ------------------------------
P001        CA002,CA004
P002        CA003
P003        CA001,CA003
P004        CA002,CA005
P005        CA001,CA004
P006        CA001,CA005
P007        CA005,CA002
P008        CA004,CA005

Previous: Count()
Next: Max()



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sqlite/aggregate-functions-and-grouping-group_concat.php