今天要解决个问题，就是实现一个MSSQL类似于mysql group_concat那样的函数，我是通过自定义聚合函数的方法实现的。
MSSQL 2005 Express
创建一个C#的数据库项目，添加一个聚合group_concat.cs，写入如下代码
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,
IsInvariantToDuplicates = false,
IsInvariantToNulls = true,
IsInvariantToOrder = false,
IsNullIfEmpty = true,
MaxByteSize = 8000
)]
public struct group_concat : IBinarySerialize
{
private StringBuilder _result;
public void Init()
{
_result = new StringBuilder();
}
public void Accumulate(SqlString Value)
{
if (Value.IsNull) return;
if (_result.Length > 0) _result.Append(",");
_result.Append(Value.Value);
}
public void Merge(group_concat Group)
{
_result.Append(Group._result);
}
public SqlString Terminate()
{
if (_result.Length > 0)
return new SqlString(_result.ToString());
return new SqlString("");
}
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
this._result = new StringBuilder(r.ReadString());
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(this._result.ToString());
}
#endregion
}
生成group_concat.dll，然后按照它：
需要开启 SQL Server 2005 对 CLR 的支持（如果没有打开的话）。
执行如下命令：
EXEC sp_configure clr enabled, 1
RECONFIGURE WITH OVERRIDE
SQL Server 2005 必须设置兼容性级别为 "SQL Server 2005(90)",
exec sp_dbcmptlevel 数据库名,90
注册程序集合
CREATE ASSEMBLY group_concat FROM F:c#实验group_concatgroup_concatinReleasegroup_concat.dll
注册聚合函数
CREATE AGGREGATE group_concat(@input nvarchar(2000)) RETURNS nvarchar(max) EXTERNAL NAME group_concat
后来在CSDN上面，又找到个解决方案，个人认为这个解决方案很牛X，呵呵，就记录一下：
-- 示例数据
DECLARE@tTABLE(id int, value varchar(10))
INSERT@tSELECT1, aa
UNIONALLSELECT1, bb
UNIONALLSELECT2, aaa
UNIONALLSELECT2, bbb
UNIONALLSELECT2, ccc
-- 查询处理
SELECT*
FROM(
SELECTDISTINCT
id
FROM@t
)A
OUTER APPLY(
SELECT
[values]=STUFF(REPLACE(REPLACE(
(
SELECT value FROM
-
<script type="text/java script">BAIDU_CLB_fillSlot("771048");
-
点击复制链接 与好友分享!
回本站首页
<script>
function copyToClipBoard(){
var clipBoardContent=document.title + '\r\n' + document.location;
clipBoardContent+='\r\n';
window.clipboardData.setData("Text",clipBoardContent);
alert("恭喜您！复制成功");
}
<script type="text/java script" id="bdshare_js" data="type=tools&uid=12732">
<script type="text/java script" id="bdshell_js">
<script type="text/java script">
var bds_config = {'snsKey':{'tsina':'2386826374','tqq':'5e544a8fdea646c5a5f3967871346eb8'}};
document.getElementById("bdshell_js").src = "http://bdimg.share.baidu.com/static/js/shell_v2.js cdnversion=" + Math.ceil(new Date()/3600000)