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
(
 Id INT PRIMARY KEY IDENTITY NOT NULL,
 FKId INT NOT NULL,
 Country VARCHAR(100)
);
INSERT INTO dbo.ConcatTest (FKId, Country)
VALUES
(1, 'UK'),
(1, 'France'),
(2, 'US'),
(3, 'Germany'),
(3, 'France'),
(3, 'UK');
SELECT *
FROM dbo.ConcatTest;

This gives us the following data.

Concatenation1

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.

https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

i.e. we run the following.

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

SELECT DISTINCT
 FKId,
 (SELECT STUFF(
 (SELECT @Delimiter + Country
 FROM dbo.ConcatTest CT2
 WHERE CT2.FKId = CT1.FKId
 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.

http://sqlblog.com/blogs/rob_farley/archive/2010/04/15/handling-special-characters-with-for-xml-path.aspx

This gives us the following result.

Concatenation2

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.

http://www.mssqltips.com/sqlservertip/2022/concat-aggregates-sql-server-clr-function/

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;
[Serializable]
[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)
 {
 _accumulator.Append(delimiter.Value);
 }
}
_accumulator.Append(value.Value);
if (value.IsNull == false)
 {
 this.IsNull = false;
 }
 }
public void Merge(ConcatMagic group)
 {
 if (_accumulator.Length > 0 & group._accumulator.Length > 0)
 {
 _accumulator.Append(_delimiter);
 }
_accumulator.Append(group._accumulator.ToString());
 }
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)
 {
 w.Write(_delimiter);
 w.Write(_accumulator.ToString());
 }
}

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
GROUP BY FKId

This gives us the following result.

Concatenation3

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

Concatenation4

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

Concatenation5

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.

Advertisements

5 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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s