SqlServerCLR聚合函数:分组连接字符串
今天在开源社区看到某个人问如何将表里某列字符串按照group by的分组连接成一个字符串，相当于字符串版的sum函数。想了半天没想出好办法。最后突然想起来SqlServer可以用CLR写函数，所以用C#写了个聚合函数。代码基本参考了msdn上下面的例子，喜欢原版的可以直接看msdn：
http://msdn.microsoft.com/zh-cn/library/ms131056.aspx
折腾步骤:
- 在VS2010里面创建一个Visual C# Sql CLR 数据库项目(数据库=>SQL Server=>C# Sql CLR 数据库项目)，创建提示中需要输入数据库信息。
注意将.Net Framework改成3.5版本，我的数据库是2008，最高支持到3.5,未来2012应该会支持.Net4.0。 - 添加一个聚合函数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, //optimizer property
14 IsInvariantToDuplicates = false, //optimizer property
15 IsInvariantToOrder = false, //optimizer property
16 MaxByteSize = 8000) //maximum size in bytes of persisted value
17 ]
18 public class JoinStr : IBinarySerialize
19 {
20 private StringBuilder _strBuilder;
21 public void Init()
22 {
23 _strBuilder = new StringBuilder();
24 }
25
26 public void Accumulate(SqlString Value)
27 {
28 if (String.IsNullOrEmpty(Value.Value)) return;
29
30 _strBuilder.Append(Value.Value + "");
31 }
32
33 public 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 public void Read(System.IO.BinaryReader r)
50 {
51 _strBuilder = new StringBuilder(r.ReadString());
52 }
53
54 public void Write(System.IO.BinaryWriter w)
55 {
56 w.Write(_strBuilder.ToString());
57 }
58 } - 右击项目点'生成'，然后右击点'部署'。直接F5也可以。
- 在VS的Test.sql里或者直接在SqlServer的管理界面里新建一个查询，写入下面的sql来启用CLR功能并测试我们创建的函数：
测试Sql
1 --启用CLR
2 sp_configure 'clr enabled',1;
3 reconfigure;
4 --看看自己写的程序集发布了没有
5 SELECT * FROM sys.assemblies;
6
7 --创建测试表
8 IF OBJECT_ID('p', 'U') IS NOT NULL
9 DROP TABLE p;
10 create table p(num int, name varchar(50));
11
12 --插入测试数据
13 insert into p values(1,'aa');
14 insert into p values(1,'ab');
15 insert into p values(2,'ba');
16 insert into p values(2,'bb');
17 insert into p values(2,'bc');
18 --测试CLR聚合函数
19 select num,dbo.JoinStr(name) from dbo.p group by num; - 如果没出什么意外的话会得到下面的结果:
1 aa ab
2 ba bb bc
