Close

Using T-SQL to Aggregate Strings

I’m a SQL Server Database Engineer by day, but I must say my blog has a lot more PowerShell and automation posts than T-SQL.  However, last week I found a really great T-SQL aggregate function that I had no idea existed, so I thought I’d share it with you.

I have been working on a project to document our SQL Server environment and create GitHub issues for things that need fixed. Issues are written in markdown so you can easily generate some pretty good looking issues with plenty of data using PowerShell. This is worth a blog post of it’s own, so keep an eye out for that soon.

Long story short I wanted a way to be able to list all the SQL Server instances on the server I was logging the issue for. I have a database with two tables, one that contains server information and one that contains instance information. Running the following gets me one row per server/instance combination.

SELECT s.ServerListId, s.ServerName, i.InstanceListId, i.InstanceName
FROM ServerList s
INNER JOIN InstanceList i
    ON s.ServerListId = i.ServerListId
ServerListIdServerNameInstanceListIdInstanceName
1MSSQL11MSSQLSERVER
2MSSQL22MSSQLSERVER
3MSSQL33MSSQLSERVER
2MSSQL24NAMEDINST1
2MSSQL25NAMEDINST2

I started thinking about how to group this data by server name and then concatenate the instance names together. Luckily a quick google found STRING_AGG(). This T-SQL aggregate function has only been available since SQL Server 2017, and does exactly what I needed. It takes two parameters, the first being the column name that should be aggregated and the second a separator to use.

For this example I’ll group by ServerName, and aggregate the InstanceName column using a comma to separate the values.

SELECT ServerName, STRING_AGG(InstanceName,', ') as InstanceName
FROM ServerList s
INNER JOIN InstanceList i
    ON s.ServerListId = i.ServerListId
GROUP BY ServerName
ServerNameInstanceName
MSSQL1MSSQLSERVER
MSSQL2MSSQLSERVER, NAMEDINST1, NAMEDINST2
MSSQL3MSSQLSERVER

Hope some of you find this quick T-SQL post useful. It definitely fit my need well for this scenario.

1 thought on “Using T-SQL to Aggregate Strings

  1. I’ve written longer code to do this for years – thank you for the tip on this much clearer and more concise method!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: