SQL Server中的concat函数
这个问题在这里已经有了答案：
＃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
如何去除最终的逗号 - 是你自己的
＃2的CLR聚合c＃代码
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
}
}
从@ OlegDok's选择的答案可能会返回正确的结果。 但表现可能会很糟糕。 这个测试场景会说明它。
创建临时表：
CREATE table #temp (EmpId INT, EmpName VARCHAR(100))
;WITH N(N)AS
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f)
INSERT #temp
SELECT EmpId, EmpName FROM (values(1, 'Mary'),(1, 'John'),(1, 'Sam')) x(EmpId, EmpName)
CROSS APPLY
(SELECT top 2000 N FROM tally) y
UNION ALL
SELECT EmpId, EmpName FROM (values(2, 'Alaina'),(2, 'Edward')) x(EmpId, EmpName)
CROSS APPLY
(SELECT top 2000 N FROM tally) y
这只有10.000行。 但很多相同的EmpId。
Oleg的答案中的这个查询在我的数据库上花了64秒。
SELECT distinct
EmpId,
(
SELECT EmpName+','
FROM #temp t2
WHERE t2.EmpId = t1.EmpId
FOR XML PATH('')
) Concatenated
FROM #temp t1
在这种情况下，区别不是清理行的正确方法。 为避免此笛卡尔连接，请在加入之前减少初始ID数。
这是处理这个问题的正确方法：
;WITH CTE as
(
SELECT distinct EmpId
FROM #temp
)
SELECT
EmpId,
STUFF((
SELECT ','+EmpName
FROM #temp t2
WHERE t2.EmpId = t1.EmpId
FOR XML PATH('')
), 1,1,'') Concatenated
FROM CTE t1
这需要不到1秒
我认为MSSQL中没有GROUP_CONCAT函数。 本文展示了concactenating行值的不同方式。
当项目数量很小并且事先已知时，连接值
SELECT CategoryId,
MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ', ' +
MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END )
FROM ( SELECT p1.CategoryId, p1.ProductName,
( SELECT COUNT(*)
FROM Northwind.dbo.Products p2
WHERE p2.CategoryId = p1.CategoryId
AND p2.ProductName <= p1.ProductName )
FROM Northwind.dbo.Products p1 ) D ( CategoryId, ProductName, seq )
GROUP BY CategoryId ;
更多关于此链接的方法。
链接地址: http://www.djcxy.com/p/44417.html