Función SQL group_concat en SQL Server [duplicado]
Si hay una tabla llamada empleado
EmpID EmpName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Resultado que necesito en este formato:
EmpID EmpName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
P: este registro está en la misma Employeetabla. Casi no tengo experiencia en el uso de UDF, procedimientos almacenados, necesito hacer esto mediante consultas. ¿Es esto posible sin usar UDF, SP?
- PARA truco y artículo XML PATH
- CLR agregado definido por el usuario
- para SQL Server versión anterior 2005 - tablas temporales
Un ejemplo del n.° 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
Cómo quitar la coma final, depende de ti
Un código C# agregado CLR para el n.º 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
}
}
La respuesta elegida de @OlegDok puede devolver el resultado correcto. Pero el rendimiento puede ser terrible. Este escenario de prueba lo ilustrará.
Creación de una tabla temporal:
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
Son sólo 10.000 filas. Pero muchos EmpId idénticos.
Esta consulta en la respuesta de Oleg tardó 64 segundos en mi base de datos.
SELECT distinct
EmpId,
(
SELECT EmpName+','
FROM #temp t2
WHERE t2.EmpId = t1.EmpId
FOR XML PATH('')
) Concatenated
FROM #temp t1
Distinto no es la forma correcta de limpiar filas en esta situación. Para evitar esta unión cartesiana, reduzca la cantidad inicial de ID antes de unirse de esta manera.
Esta es la forma correcta de manejar esto:
;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
Esto lleva menos de 1 segundo.
