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; [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 = -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) { return; } this.intermediateResult.Append(value.Value).Append(separator); } /// <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()); } }
The SQL to create the assembly and aggregate
create assembly SqlClassLibrary from N'C:\temp\SqlClassLibrary.dll' with permission_set = safe go create aggregate concatenate ( @value nvarchar(max), @separator nvarchar(10) ) returns nvarchar(max) external name SqlClassLibrary.Concatenate go
Sample usage
select module_name, table_name, concatenate(column_name, ', ') from load_file_mapping group by module_name,table_name