Hello again!

How many times have you needed to create a list of values separated by commas? I’ve been in that situation many times, and I’m sure you have too.

Before SQL Server 2017, doing this usually meant ugly XML tricks like STUFF and FOR XML PATH.

STRING_AGG()

The STRING_AGG() function lets you concatenate values using a separator of your choice — and it won’t add the separator at the end of the string.

For this example, I’ll be using the WideWorldImporters database.

SELECT STRING_AGG(CustomerName, ', ') AS CustomerList
FROM Sales.Customers;

STRING_AGG() is available starting with SQL Server 2017.

As you can see, we easily generated a comma-separated list of values.

Let’s look at another example — this one has a catch.

SELECT STRING_AGG(EmailAddress, ', ') AS EmailList
FROM Application.People;

You might see the following error:

STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

Why does this happen?

STRING_AGG() returns a data type based on the input type. If the resulting string exceeds 8,000 bytes and the input type isn’t a LOB, SQL Server throws this error.

How to fix it

The fix is simple: cast the input value to a LOB type such as NVARCHAR(MAX).

SELECT STRING_AGG(CAST(EmailAddress AS NVARCHAR(MAX)), ', ') AS EmailList
FROM Application.People;

In my case, casting EmailAddress to NVARCHAR(MAX) solved the problem. Now I can safely generate my spam marketing email list.

Grouping and Sorting

The WITHIN GROUP clause is optional — unless you care about ordering.

SELECT 
    StateProvinceID,
    STRING_AGG(CityName, ', ') AS Cities
FROM Application.Cities
GROUP BY StateProvinceID;

Notice how the values appear in a random order.

To guarantee ordering, you must use WITHIN GROUP (ORDER BY ...).

SELECT 
    StateProvinceID,
    STRING_AGG(CityName, ', ') WITHIN GROUP (ORDER BY CityName) AS Cities
FROM Application.Cities
GROUP BY StateProvinceID;

Now the list is properly sorted.

The reason we need a GROUP BY clause is simple: STRING_AGG() is an aggregate function.

Final Thoughts

STRING_AGG() provides a clean and efficient way to generate delimited lists in SQL Server. If you’re still on an older version, you’ll need to stick with the legacy XML approach — but if you’re on 2017 or newer, there’s no reason not to use it.

You can find the code for this and other posts here:
https://github.com/brodriguezv/sql-code

If you don’t have the WideWorldImporters database, you can get it here:
https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers

Thanks for reading — see you soon 🍻