In PostgreSQL, we can use the STRING_AGG() function to return columns from a query as a delimited list.

Syntax

The syntax goes like this:

string_agg ( value text, delimiter text ) → text
string_agg ( value bytea, delimiter bytea ) → bytea

We can also use the ORDER BY clause and a DISTINCT clause from within this function, which affects the output from the function. More on this below.

Example

Suppose we run the following query:

SELECT PetName 
FROM Pets;

And we get the following result:

+---------+
| petname |
+---------+
| Fluffy  |
| Fetch   |
| Scratch |
| Wag     |
| Tweet   |
| Fluffy  |
| Bark    |
| Meow    |
+---------+
(8 rows)

We can use STRING_AGG() to return all of those rows as a delimited list.

To do this, pass the PetName column as the first argument, and our chosen delimiter as the second argument:

SELECT STRING_AGG(PetName, ',') 
FROM Pets;

Result:

+-------------------------------------------------+
|                   string_agg                    |
+-------------------------------------------------+
| Fluffy,Fetch,Scratch,Wag,Tweet,Fluffy,Bark,Meow |
+-------------------------------------------------+
(1 row)

Changing the Delimiter

In the previous example, I chose a comma as the delimiter. Here it is with a different delimiter:

SELECT STRING_AGG(PetName, '-') 
FROM Pets;

Result:

Fluffy-Fetch-Scratch-Wag-Tweet-Fluffy-Bark-Meow

We can even use an empty string to remove all separators (so that the values are concatenated):

SELECT STRING_AGG(PetName, '') 
FROM Pets;

And we get the following result:

FluffyFetchScratchWagTweetFluffyBarkMeow

Ordering

We can use the ORDER BY clause within the STRING_AGG() function to order its own output:

SELECT STRING_AGG(PetName, ',' ORDER BY PetName ASC) FROM Pets;

Result:

Bark,Fetch,Fluffy,Fluffy,Meow,Scratch,Tweet,Wag

That was in ascending order.

Here it is in descending order:

SELECT STRING_AGG(PetName, ',' ORDER BY PetName DESC) FROM Pets;

Result:

Wag,Tweet,Scratch,Meow,Fluffy,Fluffy,Fetch,Bark

Note that this only sorts the output of the STRING_AGG() function – it’s completely independent of any ordering applied to the SELECT statement itself.

The DISTINCT Clause

We can use the DISTINCT clause to return unique values. In other words, if there are duplicate values, only one occurrence is returned:

SELECT STRING_AGG(DISTINCT PetName, ',' ORDER BY PetName ASC) FROM Pets;

Result:

Bark,Fetch,Fluffy,Meow,Scratch,Tweet,Wag

In this case, Fluffy only appears once. When we run it without the DISTINCT clause, Fluffy appears twice:

SELECT STRING_AGG(PetName, ',' ORDER BY PetName ASC) FROM Pets;

Result:

Bark,Fetch,Fluffy,Fluffy,Meow,Scratch,Tweet,Wag

Grouped Query Results

We can include STRING_AGG() in a query with a GROUP BY clause to achieve a result like this:

SELECT 
    PetTypeId,
    STRING_AGG(PetName, ',' ORDER BY PetName ASC)
FROM Pets
GROUP BY PetTypeId
ORDER BY PetTypeId;

Result:

+-----------+-----------------------+
| pettypeid |      string_agg       |
+-----------+-----------------------+
|         1 | Tweet                 |
|         2 | Fluffy,Meow,Scratch   |
|         3 | Bark,Fetch,Fluffy,Wag |
+-----------+-----------------------+

In my database, the actual pet type names are in another table called PetTypes. We could therefore run an INNER JOIN on the PetTypes table to get the actual pet type names:

SELECT 
    pt.PetType,
    STRING_AGG(p.PetName, ',' ORDER BY p.PetName ASC)
FROM Pets p
INNER JOIN PetTypes pt ON
p.PetTypeId = pt.PetTypeId
GROUP BY pt.PetType
ORDER BY pt.PetType ASC;

Result:

+---------+-----------------------+
| pettype |      string_agg       |
+---------+-----------------------+
| Bird    | Tweet                 |
| Cat     | Fluffy,Meow,Scratch   |
| Dog     | Bark,Fetch,Fluffy,Wag |
+---------+-----------------------+