SQLite has a group_concat()
function that allows you to concatenate multiple results returned for a column into one.
This is sometimes referred to as “string aggregation”.
For example, when querying a database, instead of having each column’s value output in a new row, you can use group_concat()
to have them output as a comma separated list.
Syntax
You can use this function in one of the following ways:
group_concat(X)
group_concat(X,Y)
In other words, the group_concat()
function can be used with either one argument or two.
The second argument specifies a character to use as the separator. If you don’t specify this, then a comma (,
) will be used as the separator.
Example with One Argument
Here’s an example of calling group_concat()
with one argument.
SELECT group_concat(FirstName)
FROM Employee;
Result:
Andrew,Nancy,Jane,Margaret,Steve,Michael,Robert,Laura
If I didn’t use group_concat()
to concatenate this column, the results would have looked like this:
SELECT FirstName
FROM Employee;
Result:
Andrew Nancy Jane Margaret Steve Michael Robert Laura
Use a Custom Separator
As mentioned, you have the option of specifying which character to use as the separator.
Here’s an example of specifying the pipe symbol (|
) as the separator.
SELECT group_concat(FirstName, '|')
FROM Employee;
Result:
Andrew|Nancy|Jane|Margaret|Steve|Michael|Robert|Laura
Here’s another one that uses a comma, but also adds a space to the right of the comma.
SELECT group_concat(FirstName, ', ')
FROM Employee;
Result:
Andrew, Nancy, Jane, Margaret, Steve, Michael, Robert, Laura
Group the Results
You can return multiple columns and use the GROUP BY
clause to group them appropriately.
Here’s an example.
SELECT ArtistId, group_concat(AlbumName)
FROM Albums
GROUP BY ArtistId LIMIT 10;
Result:
ArtistId group_concat(AlbumName)
-------- --------------------------------------------------------------------------
1 Powerslave,Somewhere in Time,Piece of Mind,Killers,No Prayer for the Dying
2 Powerage
3 All Night Wrong,The Sixteen Men of Tain
4 Big Swing Face
5 Ziltoid the Omniscient,Casualties of Cool,Epicloud
6 Singing Down the Lane
7 Long Lost Suitcase,Praise and Blame,Along Came Jones
9 No Sound Without Silence
12 Blue Night,Eternity,Scandinavia