不多说了，说明后面是完整的代码，用来将字符串型的字段的各行的值拼成一个大字符串，也就是通常所说的Concat

例如有如下表dict

 

ID	NAME	CATEGORY
1	RED	COLOR
2	BLUE	COLOR
3	APPLE	FRUIT
4	ORANGE	FRUIT

 

执行SQL语句：select category,dbo.concatenate(name) as names from dict group by category.

得到结果表如下

 

category	names
COLOR	REDBLUE
FRUIT	APPLEORANGE

 

如果觉得需要用逗号或分号或其他任何你想要的分隔符分开，可以修改下面的代码来实现。

 

在VS2005中，创建一个连接到目标库的SQL SERVER PROJECT，然后填加一个“聚合”，将下面的代码复制进去，编译后，部署即可，然后在SQL SERVER中的“可编程性”“函数”“聚合函数”中就可以看到该函数了。 

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 Concatenate : 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);

    }

    /// <summary>

    /// Merge the partially computed aggregate with this aggregate.

    /// </summary>

    /// <param name="other"></param>

    public void Merge(Concatenate 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 );

        }

        return new SqlString(output);

    }

    public void Read(BinaryReader r)

    {

        intermediateResult = new StringBuilder(r.ReadString());

    }

    public void Write(BinaryWriter w)

    {

        w.Write(this.intermediateResult.ToString());

    }

}

 

这里有几个比较重要的方法：Terminate，这个方法是聚合最后调用的方法，它返回最后的值。可以是SQL Server的任何标量；Accumulate，聚合每处理一行数据的时候都会调用一次，并将要处理的数据传给方法。可以在函数内部进行比如比较，合并之类的处理。