SQL ServerでGROUP_CONCATのようなものを使う

SQL Serverには、MySQLで言うところの GROUP_CONCAT のような文字列を結合してくれる集計関数がなくて、T-SQLでユーザ定義の集計関数を作ることもできません。でもちょっと必要に迫られたのでSQL CLRのユーザ定義関数を作ってみました。(SQLCLRは滅多に使わないのでやり方をほとんど忘れてしまっていました…)

ひとつポイントは、nvarchar(4000)のサイズを超える結果が返ってくる可能性がある場合、

public void Accumulate([SqlFacet(MaxSize=-1)]SqlString value, SqlString delimiter)

[return: SqlFacet(MaxSize = -1)]
public SqlString Terminate()

というふうに SqlFacet(MaxSize=-1) のアトリビュートを付けると、引数と戻り値をnvarchar(max)にできるようです。

あと、

[SqlUserDefinedAggregate(
    Format.UserDefined, //use clr serialization to serialize the intermediate result
    IsInvariantToNulls = true, //optimizer property
    IsInvariantToDuplicates = false, //optimizer property
    IsInvariantToOrder = false, //optimizer property
    MaxByteSize = -1) //maximum size in bytes of persisted value
]

の、 MaxByteSize = -1 の部分。
SqlFacet(MaxSize=-1)を付けないただの SqlString を使うと、nvarchar(4000)として作られてしまうので、大きい結果を返すことはできません。

以下コード全文。

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using System.Collections.Generic;

[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 = -1) //maximum size in bytes of persisted value
]
public struct GroupConcat : IBinarySerialize
{
    private string _delimiter;
    private List<string> list;

    public void Init()
    {
        this._delimiter = ",";
        this.list = new List<string>();
    }

    public void Accumulate([SqlFacet(MaxSize=-1)]SqlString value, SqlString delimiter)
    {
        if (value.IsNull) return;

        //string v = value.ToString().Replace(delimiter.ToString(), "");
        string v = value.ToString();
        this._delimiter = delimiter.ToString();
        if (!this.list.Contains(v))
            this.list.Add(v);
    }

    public void Merge(GroupConcat other)
    {
        foreach(string v in other.list)
        {
            if (!this.list.Contains(v))
                this.list.Add(v);
        }
    }

    [return: SqlFacet(MaxSize = -1)]
    public SqlString Terminate()
    {
        if (this.list.Count == 0) return SqlString.Null;

        string output = string.Join(this._delimiter, this.list.ToArray());
        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
        this._delimiter = r.ReadString();
        string output = r.ReadString();
        if (!string.IsNullOrEmpty(output))
        {
            string[] arg = output.Split(new string[] { this._delimiter }, StringSplitOptions.None);
            this.list = new List<string>(arg);
        }
        else
        {
            this.list = new List<string>();
        }
    }

    public void Write(BinaryWriter w)
    {
        string output = string.Join(this._delimiter, this.list.ToArray());
        w.Write(this._delimiter);
        w.Write(output);
    }
}

Visual Studioで SQL CLR データベースプロジェクトを作ってSQL Serverに配置して、使うときは↓

select dbo.GroupConcat(name, ', ') note from employee

こういう感じでMySQLのGROUP_CONCATと同じく区切り文字を指定できます。

SQL Server 2012の教科書 開発編