SQLCLR（五）聚合

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, // useclrserializationtoserializetheintermediateresult
IsInvariantToNulls = true , // optimizerproperty
IsInvariantToDuplicates = false , // optimizerproperty
IsInvariantToOrder = false , // optimizerproperty
MaxByteSize = 8000 ) // maximumsizeinbytesofpersistedvalue
]
public class Concatenate:IBinarySerialize
{
/**////<summary>
///Thevariablethatholdstheintermediateresultoftheconcatenation
///</summary>
privateStringBuilderintermediateResult;

/**////<summary>
///Initializetheinternaldatastructures
///</summary>
publicvoidInit()
{
this.intermediateResult=newStringBuilder();
}

/**////<summary>
///Accumulatethenextvalue,notifthevalueisnull
///</summary>
///<paramname="value"></param>
publicvoidAccumulate(SqlStringvalue)
{
if(value.IsNull)
{
return;
}

this.intermediateResult.Append(value.Value).Append(',');
}

/**////<summary>
///Mergethepartiallycomputedaggregatewiththisaggregate.
///</summary>
///<paramname="other"></param>
publicvoidMerge(Concatenateother)
{
this.intermediateResult.Append(other.intermediateResult);
}

/**////<summary>
///Calledattheendofaggregation,toreturntheresultsoftheaggregation.
///</summary>
///<returns></returns>
publicSqlStringTerminate()
{
stringoutput=string.Empty;
//deletethetrailingcomma,ifany
if(this.intermediateResult!=null
&&this.intermediateResult.Length>0)
{
output=this.intermediateResult.ToString(0,this.intermediateResult.Length-1);
}

returnnewSqlString(output);
}

publicvoidRead(BinaryReaderr)
{
intermediateResult=newStringBuilder(r.ReadString());
}

publicvoidWrite(BinaryWriterw)
{
w.Write(this.intermediateResult.ToString());
}
}

CREATE TABLE BookAuthors
(
BookID int NOT NULL ,
AuthorName nvarchar ( 200 ) NOT NULL
)

INSERT BookAuthors VALUES ( 1 , ' Johnson ' )
INSERT BookAuthors VALUES ( 2 , ' Taylor ' )
INSERT BookAuthors VALUES ( 3 , ' Steven ' )
INSERT BookAuthors VALUES ( 2 , ' Mayler ' )
INSERT BookAuthors VALUES ( 3 , ' Roberts ' )
INSERT BookAuthors VALUES ( 3 , ' Michaels ' )

SELECT BookID,dbo.MyAgg(AuthorName)
FROM BookAuthors
GROUP BY BookID