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