User defined aggregate function in SQL Server 2005

Find here the list of SQL Server aggregate functions.

Create a SQL Server project in Visual Studio 2005 or above. While creating the project provide the database information where you want to register the aggregate function.

To create an aggregate function to concatenate the values of a column to a comma separated value, use the following code. Take a look at this article for more information on the methods a user defined aggregate must implement.

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.IO; using System.Text; using System.Runtime.InteropServices; [Serializable] [StructLayout(LayoutKind.Sequential)] [SqlUserDefinedAggregate( Format.UserDefined, //use clr 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 { /// <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> public void Accumulate(SqlInt32 value) { if (value.IsNull) { return; } this.intermediateResult.Append(value.Value.ToString()).Append(‘,’); } /// <summary> /// Merge the partially computed aggregate with this aggregate. /// </summary> /// <param name=”other”></param> public void Merge(Concatenate other) { this.intermediateResult.Append(other.intermediateResult); } /// <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) { w.Write(this.intermediateResult.ToString()); } }

To enable SQL Server for CLR integration, click Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration

Click on Surface Area Configuration for Features, choose the CLR integration and check the Enable CLR integration option.

How to register the assembly in SQL Server 2005

CREATE ASSEMBLY StringUtilities FROM ‘<file path>’
GO
CREATE AGGREGATE CONCAT (@input NVARCHAR(200)) RETURNS NVARCHAR(MAX)
EXTERNAL NAME StringUtilities.Concatenate
GO
GRANT EXEC ON dbo.CONCAT TO PUBLIC
GO

Usage:

For example, below is Table1. A query on Table1 like this SELECT Column1, Column2, CONCAT(Column3) FROM Table1 GROUPBY Column1, Column2 would return a result set A,B,’1,6’.

	Column 1	Column 2	Column 3
Row 1	A	B	1
Row 2	A	B	6

Advertisement

« Applying style to a grid view row

CREATE AGGREGATE failed because type concatenate does not conform to UDAGG specification due to method ‘Accumulate’. »