# create assembly
1> CREATE ASSEMBLY Concatenate
2> FROM 'D:\\Temp\\Concatenate.dll'
3> WITH PERMISSION_SET = SAFE
4> GO
# create UDF aggregation
1> CREATE AGGREGATE Concatenate(@colName nvarchar(64))
2> RETURNS nvarchar(1000)
3> EXTERNAL NAME Concatenate.Concatenate
4> GO

# enable clr
1> sp_configure 'clr enabled', 1
2> GO
1> RECONFIGURE
2> GO

# prepare test table/data
1> CREATE TABLE BookAuthors(BookID int NOT NULL, AuthorName nvarchar(100) NOT NULL)
2> GO
1> INSERT BookAuthors VALUES(1, 'Johnson')
2> INSERT BookAuthors VALUES(1, 'Bryan')
3> INSERT BookAuthors VALUES(2, 'Taylor')
4> INSERT BookAuthors VALUES(3, 'Steven')
5> INSERT BookAuthors VALUES(2, 'Mayler')
6> INSERT BookAuthors VALUES(3, 'Roberts')
7> INSERT BookAuthors VALUES(3, 'Michaels')
8> GO

# test UDF aggregation
1> SELECT BookID, dbo.Concatenate(AuthorName)
2> FROM BookAuthors
3> GROUP BY BookID
4> GO

ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/denet9/html/5a188b50-7170-4069-acad-5de5c915f65d.htm