SQL Server 自訂彙總函式

標籤：

說明：本文依據網路轉載整理而成，因為時間關係，其中原理暫時並未深入研究，只是整理備份留個記錄而已。

 

目標：在SQL Server中自訂彙總函式，在Group BY語句中 ，不是單純的SUM和MAX等運算，可以加入拼接字串。

 

環境：

    1：Sqlserver 2008 R2  

    2：Visual Studio 2013 

 

第一部分：

.net代碼：

using System;using System.Data;using Microsoft.SqlServer.Server;using System.Data.SqlTypes;using System.IO;using System.Text;[Serializable][SqlUserDefinedAggregate(    Format.UserDefined,                 //使用UserDefined 序列化格式    IsInvariantToNulls = true,          //彙總是否與空值有關    IsInvariantToDuplicates = false,    //彙總是否與重複值有關    IsInvariantToOrder = false,         //彙總是否與順序有關    MaxByteSize = 8000)                 //彙總執行個體的最大大小(以位元組為單位)]public class Concatenate : IBinarySerialize{    /// <summary>    /// 定義變數    /// </summary>    private StringBuilder intermediateResult;    /// <summary>    /// 初始化    /// </summary>    public void Init()    {        this.intermediateResult = new StringBuilder();    }    /// <summary>    /// 如果某一個字元不為空白,用";"追加    /// </summary>    /// <param name="value"></param>    public void Accumulate(SqlString value,string contChar) //symbol    {        if (value.IsNull)        {            return;        }        this.intermediateResult.Append(value.Value).Append(contChar);    }    /// <summary>    /// 合并字元    /// </summary>    /// <param name="other"></param>    public void Merge(Concatenate other)    {        this.intermediateResult.Append(other.intermediateResult);    }    /// <summary>    /// 處理最後的","    /// </summary>    /// <returns></returns>    public SqlString Terminate()    {        string output = string.Empty;        //刪除最後的","        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());    }}

 

編譯產生DLL，注意：SqlServer 2008 R2  不支援.Net Framework 4.5 ,所以產生dll的時候選在.net framework 3.5

 

第二步：啟用資料庫對CLR支援的配置

EXEC sp_configure ‘clr enabled‘, 1RECONFIGURE WITH OVERRIDEGO

第三步：載入CLR程式集並建立自訂函數

USE Test   --選擇資料庫CREATE ASSEMBLY SQL_Aggregate FROM ‘E:\WorkSpace\LetMeTry\WindowsFormsApplication2\SqlCustomFunction\bin\Debug\SqlCustomFunction.dll‘       --產生的DLL路徑GOCREATE AGGREGATE SQL_Aggregate (@input nvarchar(200),@contChar nvarchar(1)) RETURNS nvarchar(max)EXTERNAL NAME SQL_Aggregate.Concatenate 

第四步：測試

USE Test--建立測試資料create table tb(ID int,Name varchar(10))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--自訂彙總函式使用例子（第二個參數為拼接字串的串連符）select id,dbo.SQL_Aggregate([Name],‘+‘) AS Testfrom tbgroup by id

　　

 

SQL Server 自訂彙總函式