Funzione SQL group_concat in SQL Server

Se c’è un tavolo chiamato dipendente

EmpID EmpName ---------- ------------- 1 Mary 1 John 1 Sam 2 Alaina 2 Edward 

Risultato ho bisogno in questo formato:

 EmpID EmpName ---------- ------------- 1 Mary, John, Sam 2 Alaina, Edward 

Q: questo record è nella stessa tabella Employee . Non ho quasi nessuna esperienza con le UDF, stored procedure, ho bisogno di fare questa cosa attraverso query.È ansible senza utilizzare UDF, SP.

  1. Per trucco e articolo del PERCORSO XML
  2. CLR definito dall’utente aggregato
  3. per SQL Server versione precedente 2005 – tabelle temporanee

Un esempio di # 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 

Come spogliare la virgola finale – è da solo

Un codice CL # aggregato per # 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 risposta scelta da @ OlegDok può restituire il risultato corretto. Ma la performance può essere terribile. Questo scenario di test lo illustrerà.

Creazione di una tabella temporanea:

 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 

Questo è solo 10.000 righe. Ma un sacco di identico EmpId.

Questa query nella risposta di Oleg ha impiegato 64 secondi nel mio database.

 SELECT distinct EmpId, ( SELECT EmpName+',' FROM #temp t2 WHERE t2.EmpId = t1.EmpId FOR XML PATH('') ) Concatenated FROM #temp t1 

Distinto non è il modo corretto di ripulire le file in questa situazione. Per evitare questo join cartesiano, ridurre il numero iniziale di ID prima di unirsi in questo modo.

Questo è il modo corretto di gestirlo:

 ;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 

Questo richiede meno di 1 secondo

Penso che non ci sia la funzione GROUP_CONCAT in MSSQL. Questo articolo mostra diversi modi di concatenare i valori delle righe.

Concatenazione di valori quando il numero di elementi è piccolo e noto in anticipo

 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 ; 

Più modi su questo link.

Questa è la soluzione per l’esempio dato all’inizio:

 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