SqlServer自定义聚合函数解决查询结果多行字符串拼接问题

背景

由于以前使用的PostgreSQL，自带string_agg函数可以非常方便的将查询结果聚合成一行，但是在使用SQL Server2008的项目上不能使用该函数（SQL Server2017新增了该函数），所以为了解决该问题才有以下内容；

通常可以使用 FOR XML PATH(’’) 解决部分简单聚合问题，当遇到部分复杂聚合且有大量限定条件时SQL会变得非常复杂和无逻辑关联，后期维护非常容易出错，出现遗漏的情况。基于以上原因这里需要使用到SQL Server 的自定义聚合函数；

环境：SQL Server 2008R2 VS2019

创建VS项目

1. 创建一个SQL Server 数据库项目

2. 添加一个新建项搜索聚合，选择SQL CLR C#聚合
   

3. 解决方案名右键属性调整项目配置
   目标平台调整为 SQL Server 2008
   目标框架调整为 .NET Framework 3.5(SQL Server 不支持更高版本)
   

4. 将以下代码粘贴进文件

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 string_agg : 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(string_agg 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());
    }
}

5. 点击生成解决方案或部署均可生成.dll文件

数据库配置

1. 将生成的.dll文件放到好找的地方；
2. 依次执行以下SQL命令

使用 CREATE ASSEMBLY 语句在 SQL Server 中注册程序集
根据自己dll文件位置进行修改路径字符串

CREATE ASSEMBLY StringAgg 
FROM 'D:\MSSQL_String_Agg_Function\string_agg.dll'
WITH PERMISSION_SET = SAFE;

启用 SQL Server 运行 CLR 代码的功能

exec sp_configure 'clr enabled',1
RECONFIGURE WITH OVERRIDE;

使用 CREATE AGGREGATE 语句创建引用已注册程序集的用户定义聚合函数

CREATE AGGREGATE string_agg(@input nvarchar(4000))
RETURNS nvarchar(max)
EXTERNAL NAME [StringAgg].[string_agg];

使用方法

select dbo.string_agg(f) from t group by xx

资料来源

https://gitee.com/colin_xia/stringagg
https://docs.microsoft.com/zh-cn/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-user-defined-aggregate-invoking-functions?view=sql-server-ver15

以上内容非教程，而是自己解决问题的一个记录，如有错误烦请指正