Simuler la fonction MySQL group_concat dans Microsoft SQL Server 2005 ?

Question

J'essaie de faire migrer une application basée sur MySQL vers Microsoft SQL Server 2005 (pas par choix, mais c'est la vie).

Dans l'application originale, nous avons utilisé presque entièrement conformes à la norme ANSI-SQL, à une exception près : nous avons utilisé le langage de programmation MySQL group_concat fonction assez fréquemment.

group_concat au fait, fait ceci : étant donné une table de, disons, noms d'employés et projets...

SELECT empName, projID FROM project_members;

retours :

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

... et voici ce que vous obtenez avec group_concat :

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

retours :

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

Donc ce que j'aimerais savoir c'est : Est-il possible d'écrire, disons, une fonction définie par l'utilisateur dans SQL Server qui émule la fonctionnalité de group_concat ?

Je n'ai pratiquement aucune expérience de l'utilisation d'UDF, de procédures stockées ou autres, juste du SQL pur et simple, alors n'abusez pas des explications :)

Answer 1

Il n'y a pas de véritable moyen facile de le faire. Mais il y a beaucoup d'idées.

Le meilleur que j'ai trouvé :

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;

Ou une version qui fonctionne correctement si les données peuvent contenir des caractères tels que <

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)

Answer 2

Je suis peut-être un peu en retard, mais cette méthode fonctionne pour moi et est plus facile que la méthode COALESCE.

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

Answer 3

Il est peut-être trop tard pour que cela soit utile maintenant, mais n'est-ce pas la façon la plus simple de faire les choses ?

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

Answer 4

Jetez un coup d'œil à la GROUPE_CONCAT sur Github, je pense que je fais exactement ce que vous recherchez :

Ce projet contient un ensemble de fonctions d'agrégat définies par l'utilisateur SQLCLR (SQLCLR UDAs) qui offrent collectivement une fonctionnalité similaire à la fonction GROUP_CONCAT de MySQL. Il y a plusieurs fonctions pour assurer la meilleure performance en fonction de la fonctionnalité requise...

Answer 5

Avec le code ci-dessous, vous devez définir PermissionLevel=External dans les propriétés de votre projet avant de le déployer, et modifier la base de données pour qu'elle fasse confiance au code externe (assurez-vous de lire ailleurs les risques de sécurité et les alternatives [comme les certificats]) en exécutant "ALTER DATABASE database_name SET TRUSTWORTHY ON".

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<string>
 { }

 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);
 }
    }

J'ai testé cela en utilisant une requête qui ressemble à ceci :

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 

Et les rendements : A, B, C, D