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.

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.

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

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:

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:

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

Conclusion

As you can see, STRING_AGG makes this much easier and cleaner to read. There’s less chance for mistakes too.

Thanks for reading!

---