User Defined Aggregate Function in SQL for String Concatenation using CLR

If you tried to concatenate strings from group in SQL, probably you would start looking for an aggregate function similar to SUM or COUNT. There is no function like this in SQL Server 2005 and even further there is no way how to create a custom aggregate function using SQL. You can use just built-in aggregates.

Fortunately, SQL Server 2005 integrates common language runtime (CLR). So, the solution is to write an aggregate function in C# and include it among built-in functions in SQL Server.

Let us show an example with aggregate function for strings concatenation. At first you should create a new project in VS - use template Class Library, name it for example MyCLRLib.

The key methods are:

• Init() - initializes private variables
• Accumulate() - appends the next value
• Merge() - merges partial aggregates
• Terminates() - returns a result aggregation
  

Here is completed a copy-paste code:

using System;

using System.Text;

using System.IO;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

 

[Serializable]

[SqlUserDefinedAggregate(

    Format.UserDefined,        //use custom serialization to serialize the intermediate result

    IsInvariantToNulls = true,  //optimizer property

    IsInvariantToDuplicates = false, //optimizer property

    IsInvariantToOrder = false, //optimizer property

    MaxByteSize = 8000)        //maximum size in bytes of persisted value

]

public class Concatenate : IBinarySerialize

{ 

  // Intermediate result data of concatenation

  private StringBuilder intermediateResult;

 

  public void Init()

  {

    this.intermediateResult = new StringBuilder();

  }

 

  // If the next value is not null, append it to the end of string

  public void Accumulate(SqlString value)

  {

    if (value.IsNull)

    {

      return;

    }

    this.intermediateResult.Append(value.Value).Append(", ");

  }

 

  //Merges the partial aggregate with this aggregate

  public void Merge(Concatenate part)

  {

    this.intermediateResult.Append(part.intermediateResult);

  }

 

  //Returns the result of the aggregation when finished

  public SqlString Terminate()

  {

    string result = string.Empty;   

    if (this.intermediateResult != null

        && this.intermediateResult.Length > 0)

    {

      result = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);

    }

 

    return new SqlString(result);

  }

 

  public void Read(BinaryReader reader)

  {

    intermediateResult = new StringBuilder(reader.ReadString());

  }

 

  public void Write(BinaryWriter writer)

  {

    writer.Write(this.intermediateResult.ToString());

  }

}

Now you can register this function in SQL Server. First of all, enable .NET CLR code execution in your SQL Server:

EXEC sp_configure 'clr enabled', 1;

RECONFIGURE WITH OVERRIDE;

GO

and then create new aggregate function from your compiled assembly:

CREATE ASSEMBLY MyCLRLib FROM 'D:\MyCLRLib.dll'

GO

CREATE AGGREGATE Concatenate (@input nvarchar(1000)) RETURNS nvarchar(max)

  EXTERNAL NAME MyCLRLib.Concatenate

GO

Now you can write:

SELECT Surname, dbo.Concatenate(FirstName)

FROM Families

GROUP BY Surname