How to concatenate values in multiple rows to a single string

We quite often have a requirement (mainly in our reporting) where we need to list, in a single string, a bunch of values for a particular entity that are stored in our DB as a set of rows.

For example if we use the following script…

CREATE TABLE dbo.ConcatTest
 Country VARCHAR(100)
INSERT INTO dbo.ConcatTest (FKId, Country)
(1, 'UK'),
(1, 'France'),
(2, 'US'),
(3, 'Germany'),
(3, 'France'),
(3, 'UK');
FROM dbo.ConcatTest;

This gives us the following data.


We need a way of returning one row per FKId value along with a concatenated list of related countries

The following blog from  contains several ways of doing this but the one we use almost all of the time is the FOR XML one.

i.e. we run the following.

DECLARE @Delimiter VARCHAR(10) = ' '; -- this is the delimiter we will use when we concatenate the values

 (SELECT @Delimiter + Country
 FROM dbo.ConcatTest CT2
 FOR XML PATH(''), ROOT('root'), TYPE
 ).value('/root[1]','VARCHAR(MAX)') -- using the .value method allows use to extract special characters such as &. 
 ,1,1,'')) AS Countries
FROM dbo.ConcatTest CT1;

We were originally not including the .values part but we found the following blog from Rob Farley that helped us to handle the characters that would get escaped as part of the conversion to XML.

This gives us the following result.


However, after reading the first blog I thought I’d give the CLR aggregate a try and it seems to perform a bit better and is a lot easier to use.  I found the following blog from  and decided to use this version.

Here is the final CLR aggregate I went with (pretty much pinchded from Andy).

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Server;
[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = -1)]
public struct ConcatMagic : IBinarySerialize
 private StringBuilder _accumulator;
 private string _delimiter;
 public Boolean IsNull { get; private set; }
public void Init()
 _accumulator = new StringBuilder();
 _delimiter = string.Empty;
 this.IsNull = true;
public void Accumulate(SqlString value, SqlString delimiter)
 if (!delimiter.IsNull & delimiter.Value.Length > 0)
 _delimiter = delimiter.Value;
if (_accumulator.Length > 0)
if (value.IsNull == false)
 this.IsNull = false;
public void Merge(ConcatMagic group)
 if (_accumulator.Length > 0 & group._accumulator.Length > 0)
public SqlString Terminate()
 return new SqlString(_accumulator.ToString());
void IBinarySerialize.Read(System.IO.BinaryReader r)
 _delimiter = r.ReadString();
 _accumulator = new StringBuilder(r.ReadString());
if (_accumulator.Length != 0) this.IsNull = false;
void IBinarySerialize.Write(System.IO.BinaryWriter w)

We can now run the following T-SQL to produce the same result as the FOR XML T-SQL above

DECLARE @Delimiter VARCHAR(10) = ' '; -- this is the delimiter we will use when we concatenate the values
SELECT FKId, dbo.ConcatMagic(Country, @Delimiter)
FROM dbo.ConcatTest

This gives us the following result.


Now to test the performance I’ve turned on statistics io and time and included the execution plan.  I get the following statistics. 


The number of logical reads is much more in the FOR XML T-SQL but the elapsed time is under half the time.  For larger data sets I was getting the elapsed time to be much lower for the CLR aggregate.

I get the following execution plans


I’ve zoomed into the FOR XML plan so you can see how much more complicated it is for SQL Server to process.  All the work for the aggregate is done by the CLR so obviously it’s not really appropriate here to try to compare the execution plans.  The useful metrics are the reads and time.


6 thoughts on “How to concatenate values in multiple rows to a single string

    • Thanks Rob. I did notice that as I’ve just started using it in some of the dev I’m currently doing. I’ve also noticed the code I’ve got here doesn’t de-duplicate values which can also be done easily with FOR XML if needed.

  1. Pingback: Exam 70-464 | Simon Learning SQL Server
  2. Pingback: Create CLR objects in SSDT | Simon Learning SQL Server
  3. Pingback: 70-761 Exam Prep: Miscellaneous T-SQL – Part 2 | Simon Learning SQL Server

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s