Función SQL group_concat en SQL Server

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 tabla de Employee . Casi no tengo experiencia en el uso de UDF, procedimientos almacenados, debo hacer esto a través de la consulta. Esto es posible sin usar UDF, SP’s.

  1. PARA XML PATH truco y artículo
  2. CLR agregado definido por el usuario
  3. para servidor sql versión anterior 2005 – tablas temporales

Un ejemplo de # 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 – está por su cuenta

Un CLR agregado c # código para # 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 AggregationList { get { if (_list == null) _list = new Dictionary(); return _list; } } private Dictionary _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’s 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 NN)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 

Esto es solo 10,000 filas. Pero muchos EmpId idénticos.

Esta consulta en la respuesta de Oleg tomó 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

Creo que no hay GROUP_CONCAT función GROUP_CONCAT en MSSQL. Este artículo muestra diferentes formas de concatenar los valores de fila.

Valores de concatenación cuando el número de elementos es pequeño y conocido por adelantado

 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 ; 

Más formas en este enlace.

Esta es la solución para el ejemplo dado al principio:

 SELECT DISTINCT emp_name, STUFF( (SELECT ', ' + RTRIM(proj_id) FROM project_members AS t1 WHERE t1.emp_name = t2.emp_name FOR XML PATH ('')) , 1, 1, '') FROM project_members t2