Working with the SQL Server STRING_AGG Function

Often when generating dynamic sql we must add some separator values in the string(s) we build. A function delivered way back in 2017 called STRING_AGG is a simple way to do this. Let’s look at some examples.

Feature image / license

What is STRING_AGG?

This function was introduced in SQL Server 2017. It’s purpose is to concatenate values in a string expression and place separators / delimiters between each value.

The Microsoft documentation for this function:
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16

It’s also smart enough not to add a separator at the end of the final string. Additionally, it implicitly converts to strings then concatenates.

When to Use STRING_AGG?

Looking for a use case? Let’s delve into a simple example.

Assume we are crafting some dynamic SQL string containing the names of all the databases on the instance. Perhaps we use this in a cursor to run some commands against multiple databases.

use tempdb;
go

-- let us concatenate a string of database names on our instance
SELECT * FROM sys.databases;

declare @sql nvarchar(max) = ''; --do not forget to assign to blank else null will ruin the logic
select @sql = @sql + [name]
from sys.databases;

select @sql;

use tempdb;

-- let us concatenate a string of database names on our instance

SELECT * FROM sys.databases;

declare @sql nvarchar(max) = ''; --do not forget to assign to blank else null will ruin the logic

select @sql = @sql + [name]

from sys.databases;

select @sql;

You get something like this – a big ugly string with no separators between each value:

NOTE: it is important to initialize the sql variable before we assign values to it. You can see that if we don’t initialize it some blank or some value that the default is null.

--example of what happens if you don't initialize @sql and leave it null
declare @myVar nvarchar(max);
select @myVar; -- see the default is null

--example of what happens if you don't initialize @sql and leave it null

declare @myVar nvarchar(max);

select @myVar; -- see the default is null

Now let’s add some commas to separate each value:

--now with comma separators 
declare @sql nvarchar(max) = '';
select @sql += [name] + ','
from sys.databases;

select @sql;

--now with comma separators

declare @sql nvarchar(max) = '';

select @sql += [name] + ','

from sys.databases;

select @sql;

We get the same string only this time with commas between each value. Note the comma at the end:

Now we trim the trailing comma:

--trim trailing comma
declare @sql nvarchar(max) = '';
select @sql += [name] + ','
from sys.databases;

select @sql = left(@sql,len(@sql)-1)
select @sql;

--trim trailing comma

declare @sql nvarchar(max) = '';

select @sql += [name] + ','

from sys.databases;

select @sql = left(@sql,len(@sql)-1)

select @sql;

The trimming overwrites the variable to omit the trailing comma:

Next level of improvement is to add our quoted identifiers around each database name – just in case there are spaces:

--now with quoted names
declare @sql nvarchar(max) = '';
select @sql += quotename([name]) + ','
from sys.databases;

select @sql = left(@sql,len(@sql)-1)
select @sql;

--now with quoted names

declare @sql nvarchar(max) = '';

select @sql += quotename([name]) + ','

from sys.databases;

select @sql = left(@sql,len(@sql)-1)

select @sql;

Finally, we can break out STRING_AGG to bring it home:

/*
	Now with the STRING_AGG function (introduced with SQL 2017+)
	https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16 
	Concatenates the values of string expressions and places separator values between them. The separator isn't added at the end of string.
	Null values are ignored and the corresponding separator is not added. 
*/

select string_agg(convert(nvarchar(max),[name]),',')
from sys.databases;

-- now with the square brackets
select STRING_AGG(QUOTENAME([name]),',')
from sys.databases;