SQLite group concat function

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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.

book_id book_name isbn_no cate_id aut_id pub_id dt_of_pub pub_lang no_page book_price
BK001 Introduction to Electrodynamics 0000979001 CA001 AUT001 P003 2001-05-08 English 201 85.00
BK002 Understanding of Steel Construction 0000979002 CA002 AUT002 P001 2003-07-15 English 300 105.50
BK003 Guide to Networking 0000979003 CA003 AUT003 P002 2002-09-10 Hindi 510 200.00
BK004 Transfer of Heat and Mass 0000979004 CA002 AUT004 P004 2004-02-16 English 600 250.00
BK005 Conceptual Physics 0000979005 CA001 AUT005 P006 2003-07-16   345 145.00
BK006 Fundamentals of Heat 0000979006 CA001 AUT006 P005 2003-08-10 German 247 112.00
BK007 Advanced 3d Graphics 0000979007 CA003 AUT007 P002 2004-02-16 Hindi 165 56.00
BK008 Human Anatomy 0000979008 CA005 AUT008 P006 2001-05-17 German 88 50.50
BK009 Mental Health Nursing 0000979009 CA005 AUT009 P007 2004-02-10 English 350 145.00
BK010 Fundamentals of Thermodynamics 0000979010 CA002 AUT010 P007 2002-10-14 English 400 225.00
BK011 The Experimental Analysis of Cat 0000979011 CA004 AUT011 P005 2007-06-09 French 225 95.00
BK012 The Nature of World 0000979012 CA004 AUT005 P008 2005-12-20 English 350 88.00
BK013 Environment a Sustainable Future 0000979013 CA004 AUT012 P001 2003-10-27 German 165 100.00
BK014 Concepts in Health 0000979014 CA005 AUT013 P004 2001-08-25   320 180.00
BK015 Anatomy & Physiology 0000979015 CA005 AUT014 P008 2000-10-10 Hindi 225 135.00
BK016 Networks and Telecommunications 00009790_16 CA003 AUT015 P003 2002-01-01 French 95 45.00
  1. sqlite> SELECT pub_id,group_concat(cate_id)  
  2.    ...> FROM book_mast  
  3.    ...> GROUP BY pub_id;  
  4. pub_id      group_concat(cate_id)  
  5. ----------  ---------------------  
  6. P001        CA002,CA004  
  7. P002        CA003,CA003  
  8. P003        CA001,CA003  
  9. P004        CA002,CA005  
  10. P005        CA001,CA004  
  11. P006        CA001,CA005  
  12. P007        CA005,CA002  
  13. P008        CA004,CA005  

Pictorial Presentation

example aggregate functions and grouping group_concat

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

book_id book_name isbn_no cate_id aut_id pub_id dt_of_pub pub_lang no_page book_price
BK001 Introduction to Electrodynamics 0000979001 CA001 AUT001 P003 2001-05-08 English 201 85.00
BK002 Understanding of Steel Construction 0000979002 CA002 AUT002 P001 2003-07-15 English 300 105.50
BK003 Guide to Networking 0000979003 CA003 AUT003 P002 2002-09-10 Hindi 510 200.00
BK004 Transfer of Heat and Mass 0000979004 CA002 AUT004 P004 2004-02-16 English 600 250.00
BK005 Conceptual Physics 0000979005 CA001 AUT005 P006 2003-07-16   345 145.00
BK006 Fundamentals of Heat 0000979006 CA001 AUT006 P005 2003-08-10 German 247 112.00
BK007 Advanced 3d Graphics 0000979007 CA003 AUT007 P002 2004-02-16 Hindi 165 56.00
BK008 Human Anatomy 0000979008 CA005 AUT008 P006 2001-05-17 German 88 50.50
BK009 Mental Health Nursing 0000979009 CA005 AUT009 P007 2004-02-10 English 350 145.00
BK010 Fundamentals of Thermodynamics 0000979010 CA002 AUT010 P007 2002-10-14 English 400 225.00
BK011 The Experimental Analysis of Cat 0000979011 CA004 AUT011 P005 2007-06-09 French 225 95.00
BK012 The Nature of World 0000979012 CA004 AUT005 P008 2005-12-20 English 350 88.00
BK013 Environment a Sustainable Future 0000979013 CA004 AUT012 P001 2003-10-27 German 165 100.00
BK014 Concepts in Health 0000979014 CA005 AUT013 P004 2001-08-25   320 180.00
BK015 Anatomy & Physiology 0000979015 CA005 AUT014 P008 2000-10-10 Hindi 225 135.00
BK016 Networks and Telecommunications 00009790_16 CA003 AUT015 P003 2002-01-01 French 95 45.00
  1. sqlite> SELECT pub_id,group_concat(DISTINCT cate_id)  
  2.    ...> FROM book_mast  
  3.    ...> GROUP BY pub_id  
  4.    ...> ORDER BY group_concat(DISTINCT cate_id) ASC;  
  5. pub_id      group_concat(DISTINCT cate_id)  
  6. ----------  ------------------------------  
  7. P003        CA001,CA003  
  8. P005        CA001,CA004  
  9. P006        CA001,CA005  
  10. P001        CA002,CA004  
  11. P004        CA002,CA005  
  12. P002        CA003  
  13. P008        CA004,CA005  
  14. 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

book_id book_name isbn_no cate_id aut_id pub_id dt_of_pub pub_lang no_page book_price
BK001 Introduction to Electrodynamics 0000979001 CA001 AUT001 P003 2001-05-08 English 201 85.00
BK002 Understanding of Steel Construction 0000979002 CA002 AUT002 P001 2003-07-15 English 300 105.50
BK003 Guide to Networking 0000979003 CA003 AUT003 P002 2002-09-10 Hindi 510 200.00
BK004 Transfer of Heat and Mass 0000979004 CA002 AUT004 P004 2004-02-16 English 600 250.00
BK005 Conceptual Physics 0000979005 CA001 AUT005 P006 2003-07-16   345 145.00
BK006 Fundamentals of Heat 0000979006 CA001 AUT006 P005 2003-08-10 German 247 112.00
BK007 Advanced 3d Graphics 0000979007 CA003 AUT007 P002 2004-02-16 Hindi 165 56.00
BK008 Human Anatomy 0000979008 CA005 AUT008 P006 2001-05-17 German 88 50.50
BK009 Mental Health Nursing 0000979009 CA005 AUT009 P007 2004-02-10 English 350 145.00
BK010 Fundamentals of Thermodynamics 0000979010 CA002 AUT010 P007 2002-10-14 English 400 225.00
BK011 The Experimental Analysis of Cat 0000979011 CA004 AUT011 P005 2007-06-09 French 225 95.00
BK012 The Nature of World 0000979012 CA004 AUT005 P008 2005-12-20 English 350 88.00
BK013 Environment a Sustainable Future 0000979013 CA004 AUT012 P001 2003-10-27 German 165 100.00
BK014 Concepts in Health 0000979014 CA005 AUT013 P004 2001-08-25   320 180.00
BK015 Anatomy & Physiology 0000979015 CA005 AUT014 P008 2000-10-10 Hindi 225 135.00
BK016 Networks and Telecommunications 00009790_16 CA003 AUT015 P003 2002-01-01 French 95 45.00
  1. sqlite> SELECT pub_id,group_concat(DISTINCT cate_id)  
  2.    ...> FROM book_mast  
  3.    ...> GROUP BY pub_id;  
  4. pub_id      group_concat(DISTINCT cate_id)  
  5. ----------  ------------------------------  
  6. P001        CA002,CA004  
  7. P002        CA003  
  8. P003        CA001,CA003  
  9. P004        CA002,CA005  
  10. P005        CA001,CA004  
  11. P006        CA001,CA005  
  12. P007        CA005,CA002  
  13. P008        CA004,CA005