SqlServerCLR聚合函数:分组连接字符串

今天在开源社区看到某个人问如何将表里某列字符串按照group by的分组连接成一个字符串，相当于字符串版的sum函数。想了半天没想出好办法。最后突然想起来SqlServer可以用CLR写函数，所以用C#写了个聚合函数。代码基本参考了msdn上下面的例子，喜欢原版的可以直接看msdn：
http://msdn.microsoft.com/zh-cn/library/ms131056.aspx

折腾步骤:

1. 在VS2010里面创建一个Visual C# Sql CLR 数据库项目(数据库=>SQL Server=>C# Sql CLR 数据库项目)，创建提示中需要输入数据库信息。
   注意将.Net Framework改成3.5版本，我的数据库是2008，最高支持到3.5,未来2012应该会支持.Net4.0。
2. 添加一个聚合函数JoinStr.cs:
    1 using System;
    2 using System.Data;
    3 using System.Data.SqlClient;
    4 using System.Data.SqlTypes;
    5 using Microsoft.SqlServer.Server;
    6 using System.Collections.Generic;
    7 using System.Text;
    8 
    9 
   10 [Serializable]
   11 [SqlUserDefinedAggregate(
   12     Format.UserDefined,//use clr serialization to serialize the intermediate result
   13     IsInvariantToNulls = true,0)">optimizer property14     IsInvariantToDuplicates = false,128)">15     IsInvariantToOrder = 16     MaxByteSize = 8000) maximum size in bytes of persisted value17 ]
   18 public class JoinStr : IBinarySerialize
   19 {
   20     private StringBuilder _strBuilder;
   21     void Init()
   22     {
   23         _strBuilder = new StringBuilder();
   24     }
   25 
   26     void Accumulate(SqlString Value)
   27     {
   28         if (String.IsNullOrEmpty(Value.Value)) return;
   29 
   30         _strBuilder.Append(Value.Value + "");
   31     }
   32 
   33     void Merge(JoinStr Group)
   34     {
   35         _strBuilder.Append(Group._strBuilder);
   36     }
   37 
   38     public SqlString Terminate()
   39     {
   40         string outPut = "";
   41         if (_strBuilder != null && _strBuilder.Length > 0)
   42         {
   43             outPut = _strBuilder.ToString(0,_strBuilder.Length - 2);
   44         }
   45 
   46         return new SqlString(outPut);
   47     }
   48 
   49     void Read(System.IO.BinaryReader r)
   50     {
   51         _strBuilder = new StringBuilder(r.ReadString());
   52     }
   53 
   54     void Write(System.IO.BinaryWriter w)
   55     {
   56         w.Write(_strBuilder.ToString());
   57     }
   58 }
3. 右击项目点'生成'，然后右击点'部署'。直接F5也可以。
4. 在VS的Test.sql里或者直接在SqlServer的管理界面里新建一个查询，写入下面的sql来启用CLR功能并测试我们创建的函数：
   
   1 --启用CLR
   2 sp_configure 'clr enabled',1;
   3 reconfigure;
   4 --看看自己写的程序集发布了没有
   5 SELECT * FROM sys.assemblies;
   6
   7 --创建测试表
   8 IF OBJECT_ID(pU') IS NOT NULL
   9 DROP TABLE p;
   10 create table p(num int,name varchar(50));
   11
   12 --插入测试数据
   13 insert into p values(1,0)">aa');
   14 insert into p values(ab15 insert into p values(2,0)">ba16 insert into p values(bb17 insert into p values(bc18 --测试CLR聚合函数
   19 select num,dbo.JoinStr(name) from dbo.p group by num;
5. 如果没出什么意外的话会得到下面的结果:
   

   1 aa ab 2 ba bb bc

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!