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
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics