SQL aggregate functions written in C #

SQL Server string Connection aggregation function.

2. Register assembly: Copy "SqlStrConcate.dll" to the <sql installation root directory >/mssql.1/mssql/binn directory, execute the following sql:

   CREATE ASSEMBLY [sqlstrconcate]authorization [dbo]from ' D:/program files/microsoft SQL server/mssql.1/mssql/binn/ SqlStrConcate.dll ' with permission_set = Safego

   In the above code, <sql installs the root directory > files/microsoft SQL server/for D:/program
4. To create a custom function:

   CREATE AGGREGATE strconcate (@input nvarchar) RETURNS nvarchar (max) EXTERNAL NAME sqlstrconcate.concatenate

6. Open the CLR integration for SQL Server. Method: SQL Server Surface Area Configuration, surface area configuration, and CLR integration, MSSQLSERVER Tick enable CLR integration .
8. OK, now you can use the user-defined string aggregation function strconcate . The test code is as follows:

9. ----------------------------------------------------------------Create a test table--------------------------------------------------- -----------CREATE TABLE TEST_TB (pk_val int, startdate varchar (TEN), EndDate varchar (), corpname varchar)---------- ------------------------------------------------------inserting test Data------------------------------------------------------------ --insert into TEST_TB Select 1, ' 2005-01-01 ', ' 2007-06-29 ', ' founder Technology ' Union Allselect 1, ' 2007-07-01 ', ' 2009-06-29 ', ' Tsinghua Violet ' Union allselect 1, ' 2009-01-01 ', NULL, ' UF software ' Union allselect 2, ' 1995-01-01 ', ' 2003-06-29 ', ' Microsoft China ' Union allselect 2, ' 20 04-07-01 ', ' 2009-06-29 ', ' Grand network ' go---------------------------------------------------------------- Query test--------------------------------------------------------------Select Pk_val, dbo. Strconcate (startdate + ' ~ ' + isnull (enddate, ' present ') + ': ' + corpname) lvl_str from TEST_TB Group by Pk_val---------------- ------------------------------------------------Query Results--------------------------------------------------------------pk_val lvl_str---------------------------------------------------------------------           -------------------------------1 2005-01-01~2007-06-29: Founder Technology, 2007-07-01~2009-06-29: Tsinghua Violet, 2009-01-01~ present: UF software 2 1995-01-01~2003-06-29: Microsoft China, 2004-07-01~2009-06-29: Shanda Network

11. The SqlStrConcate.dll code (online Help from SQL Server) is as follows:
12. Using System; Using System.Data; Using Microsoft.SqlServer.Server; Using System.Data.SqlTypes; Using System.IO; Using System.Text;
    [Serializable] [Sqluserdefinedaggregate (Format.userdefined,//use CLR serialization to serialize the intermediate result Isinvar Ianttonulls = True,//optimizer property Isinvarianttoduplicates = False,//optimizer property Isinvarianttoorder = False,//optimizer property MaxByteSize = 8000)//maximum size in bytes of persisted value] public class Concatenat e:ibinaryserialize {//<summary>//The variable that holds the intermediate result of the Concatenatio N//</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 (SqlString value) {if (value.         IsNull) {return; }
    This.intermediateResult.Append (value. Value).     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.intermediatere Sult.         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 ()); } }

SQL aggregate functions written in C #