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

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()