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 IsInvariantToNulls = true, //optimizer property IsInvariantToDuplicates = false, //optimizer property IsInvariantToOrder = false, //optimizer property MaxByteSize = 8000) //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> 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.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()); }
}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 BookAuthorsGROUP BY BookID| BookID | Author Names |
|---|---|
|
1 |
Johnson |
|
2 |
Taylor, Mayler |
|
3 |
Roberts, Michaels, Steven |
| 上一篇：SQLCLR（四）用户定义类型UDT | 下一篇：绝对让您的站PR值上2的方法 |
| 本站刊载此文仅为提供更多信息，不代表同意其说法，也不构成任何建议。有任何异议，请联络：web@zhoulang.net |
