# StringAgg

**Repository Path**: colin_xia/stringagg

## Basic Information

- **Project Name**: StringAgg
- **Description**: c# 实现sql server 字符串连接的聚合函数 str_agg
- **Primary Language**: C#
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 1
- **Created**: 2017-05-06
- **Last Updated**: 2023-10-26

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 用一种 Microsoft .NET Framework 语言将用户定义聚合函数定义为类

    搜索  CLR 用户定义聚合
    
```
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());
    }
}

```

# 使用 CREATE ASSEMBLY 语句在 SQL Server 中注册程序集

    搜索 程序集（数据库引擎
    
```
    CREATE ASSEMBLY StringAgg 
    FROM 'c:\StringAgg.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(fname) from t_Emp group by FDepartmentID
```