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