Simulazione della funzione MySQL di group_concat in Microsoft SQL Server 2005?

Sto provando a migrare un’app basata su MySQL su Microsoft SQL Server 2005 (non per scelta, ma è la vita).

Nell’app originale, abbiamo utilizzato quasi interamente dichiarazioni ANSI-SQL, con una significativa eccezione: abbiamo usato la funzione group_concat di MySQL abbastanza frequentemente.

group_concat , a proposito, fa questo: data una tabella di, per esempio, nomi di dipendenti e progetti …

 SELECT empName, projID FROM project_members; 

ritorna:

 ANDY | A100 ANDY | B391 ANDY | X010 TOM | A100 TOM | A510 

… ed ecco cosa ottieni con group_concat:

 SELECT empName, group_concat(projID SEPARATOR ' / ') FROM project_members GROUP BY empName; 

ritorna:

 ANDY | A100 / B391 / X010 TOM | A100 / A510 

Quindi quello che mi piacerebbe sapere è: è ansible scrivere, ad esempio, una funzione definita dall’utente in SQL Server che emula la funzionalità di group_concat ?

Non ho quasi nessuna esperienza con le UDF, le stored procedure o qualcosa del genere, semplicemente SQL, quindi per favore sbagliate dalla parte di troppe spiegazioni 🙂

Nessun vero e semplice modo per farlo. Ci sono un sacco di idee là fuori, però.

Il migliore che ho trovato :

 SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_names FROM information_schema.columns AS extern CROSS APPLY ( SELECT column_name + ',' FROM information_schema.columns AS intern WHERE extern.table_name = intern.table_name FOR XML PATH('') ) pre_trimmed (column_names) GROUP BY table_name, column_names; 

O una versione che funziona correttamente se i dati potrebbero contenere caratteri come <

 WITH extern AS (SELECT DISTINCT table_name FROM INFORMATION_SCHEMA.COLUMNS) SELECT table_name, LEFT(y.column_names, LEN(y.column_names) - 1) AS column_names FROM extern CROSS APPLY (SELECT column_name + ',' FROM INFORMATION_SCHEMA.COLUMNS AS intern WHERE extern.table_name = intern.table_name FOR XML PATH(''), TYPE) x (column_names) CROSS APPLY (SELECT x.column_names.value('.', 'NVARCHAR(MAX)')) y(column_names) 

Potrei essere un po ‘in ritardo per la festa, ma questo metodo funziona per me ed è più facile del metodo COALESCE.

 SELECT STUFF( (SELECT ',' + Column_Name FROM Table_Name FOR XML PATH ('')) , 1, 1, '') 

Forse è troppo tardi per essere di beneficio ora, ma questo non è il modo più semplice per fare le cose?

 SELECT empName, projIDs = replace ((SELECT Surname AS [data()] FROM project_members WHERE empName = a.empName ORDER BY empName FOR xml path('')), ' ', REQUIRED SEPERATOR) FROM project_members a WHERE empName IS NOT NULL GROUP BY empName 

SQL Server 2017 introduce una nuova funzione di aggregazione

STRING_AGG ( expression, separator) .

Concatena i valori delle espressioni stringa e inserisce i valori separatori tra loro. Il separatore non viene aggiunto alla fine della stringa.

Gli elementi concatenati possono essere ordinati aggiungendo WITHIN GROUP (ORDER BY some_expression)

Per le versioni 2005-2016 generalmente utilizzo il metodo XML nella risposta accettata.

Questo può fallire in alcune circostanze comunque. per esempio se i dati da concatenare contengono CHAR(29) che vedi

FOR XML non ha potuto serializzare i dati … perché contiene un carattere (0x001D) che non è consentito in XML.

Un metodo più robusto in grado di gestire tutti i caratteri consiste nell’utilizzare un aggregato CLR. Tuttavia applicare un ordine agli elementi concatenati è più difficile con questo approccio.

Il metodo di assegnazione a una variabile non è garantito e dovrebbe essere evitato nel codice di produzione.

Dai un’occhiata al progetto GROUP_CONCAT su Github, penso di fare esattamente quello che stai cercando:

Questo progetto contiene un set di funzioni di aggregazione definite dall’utente SQLCLR (UDA SQLCLR) che offrono collettivamente funzionalità simili alla funzione MySQL GROUP_CONCAT. Ci sono più funzioni per garantire le migliori prestazioni in base alle funzionalità richieste …

Con il codice seguente è necessario impostare PermissionLevel = External sulle proprietà del progetto prima della distribuzione e modificare il database in modo da fidarsi del codice esterno (assicurarsi di leggere altrove sui rischi per la sicurezza e sulle alternative [come certificati]) eseguendo “ALTER DATABASE database_name SET FIDUCIA ATTUALE “.

 using System; using System.Collections.Generic; using System.Data.SqlTypes; using System.IO; using System.Runtime.Serialization; using System.Runtime.Serialization.Formatters.Binary; using Microsoft.SqlServer.Server; [Serializable] [SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=8000, IsInvariantToDuplicates=true, IsInvariantToNulls=true, IsInvariantToOrder=true, IsNullIfEmpty=true)] public struct CommaDelimit : IBinarySerialize { [Serializable] private class StringList : List { } private StringList List; public void Init() { this.List = new StringList(); } public void Accumulate(SqlString value) { if (!value.IsNull) this.Add(value.Value); } private void Add(string value) { if (!this.List.Contains(value)) this.List.Add(value); } public void Merge(CommaDelimit group) { foreach (string s in group.List) { this.Add(s); } } void IBinarySerialize.Read(BinaryReader reader) { IFormatter formatter = new BinaryFormatter(); this.List = (StringList)formatter.Deserialize(reader.BaseStream); } public SqlString Terminate() { if (this.List.Count == 0) return SqlString.Null; const string Separator = ", "; this.List.Sort(); return new SqlString(String.Join(Separator, this.List.ToArray())); } void IBinarySerialize.Write(BinaryWriter writer) { IFormatter formatter = new BinaryFormatter(); formatter.Serialize(writer.BaseStream, this.List); } } 

Ho provato questo usando una query che assomiglia a:

 SELECT dbo.CommaDelimit(X.value) [delimited] FROM ( SELECT 'D' [value] UNION ALL SELECT 'B' [value] UNION ALL SELECT 'B' [value] -- intentional duplicate UNION ALL SELECT 'A' [value] UNION ALL SELECT 'C' [value] ) X 

E rese: A, B, C, D

Ho provato questi, ma per i miei scopi in MS SQL Server 2005 il seguente è stato molto utile, che ho trovato su xaprb

 declare @result varchar(8000); set @result = ''; select @result = @result + name + ' ' from master.dbo.systypes; select rtrim(@result); 

@Mark come hai detto è stato il personaggio spaziale che ha causato problemi per me.

Per concatenare tutti i nomi dei project manager dai progetti che hanno più project manager scrivi:

 SELECT a.project_id,a.project_name,Stuff((SELECT N'/ ' + first_name + ', '+last_name FROM projects_v where a.project_id=project_id FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'' ) mgr_names from projects_v a group by a.project_id,a.project_name 

A proposito della risposta di J Hardiman, che ne dite di:

 SELECT empName, projIDs= REPLACE( REPLACE( (SELECT REPLACE(projID, ' ', '-somebody-puts-microsoft-out-of-his-misery-please-') AS [data()] FROM project_members WHERE empName=a.empName FOR XML PATH('')), ' ', ' / '), '-somebody-puts-microsoft-out-of-his-misery-please-', ' ') FROM project_members a WHERE empName IS NOT NULL GROUP BY empName 

A proposito, l’uso di “Cognome” è un errore o non sto capendo un concetto qui?

Comunque, grazie mille ragazzi perchè mi ha salvato parecchio tempo 🙂