/*
Автор: Колосов В.В. (создана по материалам интернет)
Дата создания: 26.07.2018
Назначение: агрегатная функция для сбора колонки в строку с разделителями
Пример использования:

create table #tt1(id int, nn varchar(10))
insert #tt1 values (1, 'qwe'),(1,'qwe'),(1,'dsa'),(2,'zxc'),(3,'tre'),(3,'tre1')
select id, dbo.ConcatColumn(nn,' + ') from (select distinct * from #tt1) tt1 group by id
 */
using System;
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 ConcatColumn : IBinarySerialize
{
    /// <summary>  
    /// The variable that holds the intermediate result of the concatenation  
    /// </summary>  
    private StringBuilder intermediateResult;
    private string _delimiter;

    /// <summary>  
    /// Initialize the internal data structures  
    /// </summary>  
    public void Init()
    {
        _delimiter = string.Empty;
        intermediateResult = new StringBuilder();
    }

    /// <summary>  
    /// Accumulate the next value, not if the value is null  
    /// </summary>  
    /// <param name="Value"></param>  
    public void Accumulate(SqlString Value, SqlString Delimiter)
    {
        if (Value.IsNull)
        {
            return;
        }

        intermediateResult.Append(Value.Value);

        if (!Delimiter.IsNull & Delimiter.Value.Length > 0)
        {
            _delimiter = Delimiter.Value; /// save for Merge
            if (intermediateResult.Length > 0) intermediateResult.Append(Delimiter.Value);
        }
    }

    /// <summary>  
    /// Merge the partially computed aggregate with this aggregate.  
    /// </summary>  
    /// <param name="other"></param>  
    public void Merge(ConcatColumn other)
    {
        if (intermediateResult.Length > 0 & other.intermediateResult.Length > 0)
            intermediateResult.Append(_delimiter);

        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 (intermediateResult != null
            && intermediateResult.Length > 0)
        {
            output = intermediateResult.ToString(0, intermediateResult.Length - _delimiter.Length);
        }

        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
        _delimiter = r.ReadString();
        intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(_delimiter);
        w.Write(this.intermediateResult.ToString());
    }
}