SQL Server 字符串连接聚合函数.

  1. 注册程序集: 拷贝“SqlStrConcate.dll”至<sql安装根目录>/MSSQL.1/MSSQL/Binn目录下,执行下面的SQL:
    CREATE ASSEMBLY [SqlStrConcate]
    AUTHORIZATION [dbo]
    FROM 'D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Binn/SqlStrConcate.dll'
    WITH PERMISSION_SET = SAFE
    GO
    上面的代码中, <sql安装根目录>为D:/Program Files/Microsoft SQL Server/
  2. 创建自定义函数:
    CREATE AGGREGATE StrConcate (@input nvarchar(200)) RETURNS nvarchar(max)
    EXTERNAL NAME SqlStrConcate.Concatenate
  3. 打开SQL Server 的CLR集成.方法: SQL Server 外围应用配置器 -> 功能的外围应用配置器 -> MSSQLSERVER -> Database Engine -> CLR集成 。勾选启用CLR集成
  4. OK了,现在你可以使用用户自定义字符串聚合函数StrConcate.测试代码如下:
  5. --------------------------------------------------------------
    -- 创建测试表
    --------------------------------------------------------------
    create table test_tb(pk_val  int, startdate varchar(10), enddate VARCHAR(10), corpname VARCHAR(20))
    
    --------------------------------------------------------------
    -- 插入测试数据
    --------------------------------------------------------------
    insert into test_tb     
    select 1, '2005-01-01', '2007-06-29', '方正科技'
    union all
    select 1, '2007-07-01', '2009-06-29', '清华紫光' 
    union all
    select 1, '2009-01-01', null, '用友软件'
    union all
    select 2, '1995-01-01', '2003-06-29', '微软中国'
    union all
    select 2, '2004-07-01', '2009-06-29', '盛大网络'  
    go
    
    --------------------------------------------------------------
    -- 查询测试
    --------------------------------------------------------------
    select pk_val, dbo.StrConcate(startdate + '~' + isnull(enddate, '至今') + ':' + corpname) lvl_str from test_tb group by pk_val
     
    --------------------------------------------------------------
    -- 查询结果
    --------------------------------------------------------------
    pk_val      lvl_str
    ----------- -----------------------------------------------------------------------------------------
    1           2005-01-01~2007-06-29:方正科技,2007-07-01~2009-06-29:清华紫光,2009-01-01~至今:用友软件
    2           1995-01-01~2003-06-29:微软中国,2004-07-01~2009-06-29:盛大网络
    
  6. SqlStrConcate.dll 代码(来自Sql Server的联机帮助)如下:
  7. 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).Append(',');     }
        /// <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 - 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());     } }