在SQL Server SQL GROUP_CONCAT功能 [英] SQL group_concat function in SQL Server
本文介绍了在SQL Server SQL GROUP_CONCAT功能的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果有一个表中调用员工
的EmpID EmpName
---------- -------------
1玛丽
约翰一书
1山姆
2 ALAINA
2爱德华
结果我需要格式为:
的EmpID EmpName
---------- -------------
1玛丽,约翰,萨姆
2 ALAINA,爱德华
问:这个纪录是在同一个员工
表。我有使用UDF的,存储过程,我需要做的工作,通过这个query.Is可能这个东西不使用的UDF,SP公司几乎没有任何经验。
解决方案
- FOR XML PATH <一个href=\"http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005\">trick和文章
- CLR用户定义的聚合
- 对于SQL Server 2005以前的版本 - 临时表
#1的例子
DECLARE @t TABLE(EMPID INT,EmpName VARCHAR(100))
INSERT @t VALUES
(1,'玛丽'),(1,'约翰'),(1,'萨姆'),(2,'ALAINA'),(2,爱德华)
SELECT DISTINCT
EMPID,
(
SELECT EmpName +','
从@t T2
WHERE t2.EmpId = t1.EmpId
FOR XML PATH('')
)级联
从@t T1
如何剥离最后一个逗号 - 是你自己的。
一个CLR聚合C#code#2
使用系统;
使用System.Collections.Generic;
使用System.Data.SqlTypes中;
使用System.Text;
使用Microsoft.SqlServer.Server;
使用System.IO;命名空间DatabaseAssembly
{
[Serializable接口]
[SqlUserDefinedAggregate(Format.UserDefined,
IsInvariantToNulls = TRUE,
IsInvariantToDuplicates = TRUE,
IsInvariantToOrder = TRUE,
MaxByteSize = -1)]
公共结构StringJoin:IBinarySerialize
{
私人字典&LT;字符串,字符串&GT; AggregationList
{
得到
{
如果(_list == NULL)
_list =新词典&LT;字符串,字符串&GT;();
返回_list;
}
}
私人字典&LT;字符串,字符串&GT; _list; 公共无效的init()
{ } 公共无效累加(的SqlString值)
{
如果(!Value.IsNull)
AggregationList [Value.Value.ToLowerInvariant()] = Value.Value; } 公共无效合并(StringJoin集团)
{
的foreach(在Group.AggregationList.Keys VAR键)
AggregationList [关键] = Group.AggregationList [关键]
} 公共SqlChars终止()
{
VAR SB =新的StringBuilder();
的foreach(在AggregationList.Values VAR值)
sb.Append(值);
返回新SqlChars(sb.ToString());
} #区域IBinarySerialize成员 公共无效读取(System.IO.BinaryReader R)
{ 尝试
{
而(真)
AggregationList [r.ReadString()] = r.ReadString();
}
赶上(EndOfStreamException)
{ }
} 公共无效写入(System.IO.BinaryWriter W)
{
的foreach(在AggregationList.Keys VAR键)
{
w.Write(键);
w.Write(AggregationList [关键]);
}
} #endregion
}
}
If there is a table called employee
EmpID EmpName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Result I need in this format:
EmpID EmpName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
Q: this record is in same Employee
table. I have almost no experience using UDFs, stored procedures, I need to be done this thing through query.Is this possible without using UDFs, SP's.
解决方案
- FOR XML PATH trick and article
- CLR User defined aggregate
- for sql server prior version 2005 - temporary tables
An example of #1
DECLARE @t TABLE (EmpId INT, EmpName VARCHAR(100))
INSERT @t VALUES
(1, 'Mary'),(1, 'John'),(1, 'Sam'),(2, 'Alaina'),(2, 'Edward')
SELECT distinct
EmpId,
(
SELECT EmpName+','
FROM @t t2
WHERE t2.EmpId = t1.EmpId
FOR XML PATH('')
) Concatenated
FROM @t t1
How to strip the final comma - is on your own
A CLR aggregate c# code for #2
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
using System.IO;
namespace DatabaseAssembly
{
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = true,
IsInvariantToOrder = true,
MaxByteSize = -1)]
public struct StringJoin : IBinarySerialize
{
private Dictionary<string, string> AggregationList
{
get
{
if (_list == null)
_list = new Dictionary<string, string>();
return _list;
}
}
private Dictionary<string, string> _list;
public void Init()
{
}
public void Accumulate(SqlString Value)
{
if (!Value.IsNull)
AggregationList[Value.Value.ToLowerInvariant()] = Value.Value;
}
public void Merge(StringJoin Group)
{
foreach (var key in Group.AggregationList.Keys)
AggregationList[key] = Group.AggregationList[key];
}
public SqlChars Terminate()
{
var sb = new StringBuilder();
foreach (var value in AggregationList.Values)
sb.Append(value);
return new SqlChars(sb.ToString());
}
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
try
{
while (true)
AggregationList[r.ReadString()] = r.ReadString();
}
catch (EndOfStreamException)
{
}
}
public void Write(System.IO.BinaryWriter w)
{
foreach (var key in AggregationList.Keys)
{
w.Write(key);
w.Write(AggregationList[key]);
}
}
#endregion
}
}
这篇关于在SQL Server SQL GROUP_CONCAT功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文