SQL Server 2017推出STRING_AGG 字串集合的串接函式,但限制是SQL2017的版本。上個月重新加入一個大型專案中,專案的版本要求是SQL 2016,Code Review時發現同事改用For Xml Path再Distinct的替代,T-SQL閱讀起來多了點複雜,捲手來救援T-SQL程式的可讀性。
因為只是少量字串資料的串接,一開始打算的替代方案是SQLCLR。有趣的是,微軟Docs示範CLR User-Defined Aggregate的範例就是字串集合的串接,哈哈,正所謂踏破鐵鞋無覓處,得來全不費功夫就是這個概念了,程式一刀未剪馬上可以用,來筆記這個開心。
開發SQLCLR
打開地表最強IDE的Visual Studio,熱鍵Ctrl + Shift + N新增一個資料庫專案,在左手邊的技術分類中,選SQL Server
熱鍵Ctrl + Shift + A 新增項目,從項目清單中選 SQL CLR C#彙總
填入Docs上Example 1的範例程式碼:
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());
}
}
熱鍵F6建置專案,接著切到bin的目錄,我們會發現有3個檔案(.dacpac .dll .pdb)
佈署SQLCLR及測試
可以用.dacpac佈署,但我們簡單使用"從dll建立組件"
CREATE ASSEMBLY MyAgg FROM 'C:\Users\stanley\Documents\Visual Studio 2017\Projects\Database1\Database1\bin\Debug\Database1.dll'
WITH PERMISSION_SET = SAFE;
GO
CREATE AGGREGATE MyAgg (@input nvarchar(200)) RETURNS nvarchar(max)
EXTERNAL NAME MyAgg.Concatenate;
建立完畢後,在指定資料庫的可程式性下,可以發現多了我們建立的組件與彙總函式
建立測試資料表並新增6筆測試資料
CREATE TABLE BookAuthors
(
BookID int NOT NULL,
AuthorName nvarchar(200) NOT NULL
);
INSERT BookAuthors VALUES(1, 'Johnson'),(2, 'Taylor'),(3, 'Steven'),(2, 'Mayler'),(3, 'Roberts'),(3, 'Michaels');
測試一下字串運算式值的串接,只要用使用MyAgg函式
SELECT BookID, dbo.MyAgg(AuthorName)
FROM BookAuthors
GROUP BY BookID;
依照BookID分組,將字串集合串接
xml path作法
SELECT DISTINCT
BookID
,STUFF((SELECT
',' + AuthorName
FROM BookAuthors A
WHERE A.BookID = B.BookID
FOR XML PATH (''))
, 1, 1, '') AS AuthorNames
FROM BookAuthors B
小結
SQL CLR VS XML Path
深夜寫blog就是要熬到看歐冠決賽! (西甲皇馬 vs 英超利物浦)
參考
CREATE ASSEMBLY (Transact-SQL)