SQL Server Custom Aggregate functions

Description: This article is based on the network reprint collation, because of the time relationship, wherein the principle is not in-depth study, just to tidy up the backup to leave a record.

Objective: To customize the aggregate function in SQL Server, in the group by statement, not just the sum and max operations, you can join the stitching string.

Environment:

1:sqlserver R2

2:visual Studio 2013

The first part:

. NET code:

Using system;using system.data;using microsoft.sqlserver.server;using system.data.sqltypes;using System.IO;using System.Text; [Serializable]          [Sqluserdefinedaggregate (format.userdefined,//Use userdefined serialization Format Isinvarianttonulls = True,    Whether the aggregation is related to null values Isinvarianttoduplicates = False, whether the aggregation is related to duplicate values Isinvarianttoorder = False,//whether the aggregation is related to order MaxByteSize = 8000)//maximum size (in bytes) of the aggregate instance]public class concatenate:ibinaryserialize{//<summary&gt    ;    Define variables///</summary> private StringBuilder intermediateresult; <summary>///Initialize///</summary> public void Init () {this.intermediateresult = new Str    Ingbuilder (); }///<summary>//If a character is not empty, use ";" Additional//</summary>//<param name= "value" ></param> public void accumulate (SqlString Value,strin G Contchar)//symbol {if (value.        IsNull) {return; } this.iNtermediateresult.append (value. Value).    Append (Contchar); }///<summary>//<param name= "Other" ></param> public voi    D Merge (concatenate other) {this.intermediateResult.Append (Other.intermediateresult); }///<summary>//For final ","///</summary>//<returns></returns> public Sqlstrin G Terminate () {string output = string.        Empty;        Delete the last "," 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 ()); }}

Compile build DLL, note: SQL Server R2 does not support the. NET Framework 4.5, so when generating DLLs, select the. NET Framework 3.5

Step two: Enable database-to-CLR support configuration

EXEC sp_configure ' clr enabled ', 1RECONFIGURE with Overridego

Step three: Load CLR assemblies and create custom functions

Use Test   --Select database Create ASSEMBLY sql_aggregate from ' E:\WorkSpace\LetMeTry\WindowsFormsApplication2\ Sqlcustomfunction\bin\debug\sqlcustomfunction.dll '       

Fourth Step: Test

Use test--Creating test data CREATE Table TB (ID int,name varchar) INSERT into TB Select 1, ' A ' union ALL select 1, ' B ' UNION ALL Select 2, ' C ' union ALL select 2, ' E ' union ALL select 3, ' d ' go--custom aggregate function using an example (the second parameter is a concatenation of concatenated strings) select Id,dbo. Sql_aggregate ([Name], ' + ') as Testfrom Tbgroup by ID

　　

SQL Server Custom Aggregate functions