CLR User-Defined Aggregates: Concatenate strings with separator

This is basically the MSDN sample with the addition of an user-supplied separator. For some reason I couldn’t get this to deploy successfully from within visual studio I had to create aggregate manually inside SQL Server (2008).

The code for the aggregate

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

 Format.UserDefined, //use custom serialization to serialize the intermediate result
 IsInvariantToNulls = true, //optimizer property
 IsInvariantToDuplicates = false, //optimizer property
 IsInvariantToOrder = false, //optimizer property
 MaxByteSize = -1) //maximum size in bytes of persisted value
public class Concatenate : IBinarySerialize
 /// <summary>
 /// The variable that holds the intermediate result of the concatenation
 /// </summary>
 private StringBuilder intermediateResult;
 /// <summary>
 /// Initialize the internal data structures
 /// </summary>
 public void Init()
 this.intermediateResult = new StringBuilder();
 /// <summary>
 /// Accumulate the next value, not if the value is null
 /// </summary>
 /// <param name="value"></param>
 /// <param name="separator"></param>
 public void Accumulate(SqlString value, SqlString separator)
 if (value.IsNull)


 /// <summary>
 /// Merge the partially computed aggregate with this aggregate.
 /// </summary>
 /// <param name="other"></param>
 public void Merge(Concatenate other)

 /// <summary>
 /// Called at the end of aggregation, to return the results of the aggregation.
 /// </summary>
 /// <returns></returns>
 public SqlString Terminate()
 string output = string.Empty;
 //delete the trailing comma, if any
 if (this.intermediateResult != null
 && this.intermediateResult.Length > 0)
 output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);

 return new SqlString(output);

 public void Read(BinaryReader r)
 intermediateResult = new StringBuilder(r.ReadString());

 public void Write(BinaryWriter w)

The  SQL to create the assembly and aggregate

create assembly SqlClassLibrary
 from N'C:\temp\SqlClassLibrary.dll'
 with permission_set = safe

create aggregate concatenate (
 @value nvarchar(max),
 @separator nvarchar(10)
) returns nvarchar(max)
external name SqlClassLibrary.Concatenate


Sample usage

select module_name, table_name,
 concatenate(column_name, ', ')
from load_file_mapping
group by module_name,table_name

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