GROUP_CONCAT Example

Here have a table called web_contacts which lists email addresses of individuals interested in different topics. We want to generate a list of email addresses of the "sqlite" group which we can paste into an email program.

sqlite>.headers on
sqlite>.mode columns
sqlite> SELECT * FROM web_contacts;

FirstName LastName ListName Email
---------- ---------- ---------- -------------------
Peter Nelson sqlite pnelson@oldmail.net
Alan Reed sqlite aj.reed@oldmail.net
James Doe xslt james.doe@myemail.o
Roberta Allen xslt r.allen@myemail.org
George Millford sqlite gpmillford@myemail.
Kim Simpson html kimberly.simpson@my

GROUP_CONCAT will create a comma delimited string of the values in the Email column with the following syntax.

SELECT GROUP_CONCAT(Email FROM'web_contacts') WHERE ListName='sqlite';

sqlite>.mode list
sqlite> SELECT GROUP_CONCAT(Email) FROM web_contacts WHERE ListName='sqlite';
GROUP_CONCAT(Email)
pnelson@oldmail.net,aj.reed@oldmail.net,gpmillford@myemail.org
sqlite>

You can also specify a delimiter other than a comma with the following syntax.

SELECT GROUP_CONCAT(column_name,'delimiter') FROM table_name;

SELECT GROUP_CONCAT(Email,'~') FROM web_contacts WHERE ListName='sqlite';
GROUP_CONCAT(Email,'~')
pnelson@oldmail.net~aj.reed@oldmail.net~gpmillford@myemail.org
sqlite>

In the example shown above, the specified delimiter is a tilde enclosed in single quotes which follows the column name.

Table Data for web_contacts table

CREATE TABLE web_contacts(FirstName TEXT,LastName TEXT,ListName TEXT,Email TEXT);
INSERT INTO web_contacts (FirstName,LastName,ListName,Email) VALUES('Peter','Nelson','sqlite','pnelson@oldmail.net');
INSERT INTO web_contacts (FirstName,LastName,ListName,Email) VALUES('Alan','Reed','sqlite','aj.reed@oldmail.net');
INSERT INTO web_contacts (FirstName,LastName,ListName,Email) VALUES('James','Doe','xslt','james.doe@myemail.org');
INSERT INTO web_contacts (FirstName,LastName,ListName,Email) VALUES('Roberta','Allen','xslt','r.allen@myemail.org');
INSERT INTO web_contacts (FirstName,LastName,ListName,Email) VALUES('George','Millford','sqlite','gpmillford@myemail.org');
INSERT INTO web_contacts (FirstName,LastName,ListName,Email) VALUES('Kim','Simpson','html','kimberly.simpson@myemail.org');


Home     SQLite Site Index