Mssql 2005 的 group_concat

时间：2011-01-16 来源：秩名

今天要解决个问题，就是实现一个MSSQL类似于mysql group_concat那样的函数，我是通过自定义聚合函数的方法实现的。

MSSQL 2005 Express

创建一个C#的数据库项目，添加一个聚合group_concat.cs，写入如下代码

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,
    IsInvariantToDuplicates = false,
    IsInvariantToNulls = true,
    IsInvariantToOrder = false,
    IsNullIfEmpty = true,
    MaxByteSize = 8000
)]

public struct group_concat : IBinarySerialize
{
    private StringBuilder _result;
    public void Init()
    {
        _result = new StringBuilder();
    }

    public void Accumulate(SqlString Value)
    {
        if (Value.IsNull) return;
        if (_result.Length > 0) _result.Append(",");
        _result.Append(Value.Value);
    }

    public void Merge(group_concat Group)
    {
        _result.Append(Group._result);
    }

    public SqlString Terminate()
    {
        if (_result.Length > 0)
            return new SqlString(_result.ToString());
        return new SqlString("");
    }

    #region IBinarySerialize Members

    public void Read(System.IO.BinaryReader r)
    {
        this._result = new StringBuilder(r.ReadString());
    }

    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(this._result.ToString());
    }
   #endregion
}