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
Alan Reed sqlite
James Doe xslt james.doe@myemail.o
Roberta Allen xslt
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';

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';

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','');
INSERT INTO web_contacts (FirstName,LastName,ListName,Email) VALUES('Alan','Reed','sqlite','');
INSERT INTO web_contacts (FirstName,LastName,ListName,Email) VALUES('James','Doe','xslt','');
INSERT INTO web_contacts (FirstName,LastName,ListName,Email) VALUES('Roberta','Allen','xslt','');
INSERT INTO web_contacts (FirstName,LastName,ListName,Email) VALUES('George','Millford','sqlite','');
INSERT INTO web_contacts (FirstName,LastName,ListName,Email) VALUES('Kim','Simpson','html','');

Home     SQLite Site Index