Simulation de la fonction MySQL de concat de groupe dans Microsoft SQL Server 2005?

j'essaie de 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 déclarations entièrement conformes ANSI-SQL, avec une exception significative -- nous avons utilisé la fonction group_concat de MySQL assez fréquemment.

group_concat , d'ailleurs, fait ceci: donné un tableau de, disons, les noms des employés et des projets...

SELECT empName, projID FROM project_members;

retourne:

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;

retourne:

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

alors 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 presque aucune expérience de L'utilisation D'UDFs, procédures stockées, ou n'importe quoi de ce genre, juste SQL, donc s'il vous plaît erreur sur le côté de trop d'explication :)

316
demandé sur Salman A 2009-01-16 21:14:52

10 réponses

ce n'est pas un moyen facile. Beaucoup d'idées là-bas, cependant.

Meilleure 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) 
155
répondu BradC 2014-12-09 22:19:03

je suis peut-être un peu en retard au parti 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, '')
155
répondu Scott 2012-01-29 15:13:16

peut-être trop tard pour en profiter maintenant, mais n'est-ce pas la façon la plus facile 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
43
répondu J Hardiman 2010-02-24 06:24:11

SQL Server 2017 introduit une nouvelle fonction d'agrégation

STRING_AGG ( expression, separator) .

Concaténate les valeurs des expressions de chaîne et séparateur de lieux des valeurs entre eux. Le séparateur n'est pas ajouté à la fin de la chaîne.

les éléments concaténés peuvent être commandés en ajoutant WITHIN GROUP (ORDER BY some_expression)

Pour les versions 2005-2016 j'utilise habituellement la méthode XML dans la réponse acceptée.

cela peut échouer dans certaines circonstances cependant. par exemple, si les données à concaténer contiennent CHAR(29) vous voyez

pour XML ne pouvait pas sérialiser les données ... parce qu'il contient un caractère (0x001D) qui n'est pas autorisé en XML.

une méthode plus robuste qui peut traiter avec tous les caractères serait de utilisez un agrégat CLR. Toutefois, appliquer une commande aux éléments concaténés est plus difficile avec cette approche.

la méthode d'affectation à une variable est non garanti et doit être évitée dans le code de production.

28
répondu Martin Smith 2017-11-01 21:06:12

regardez le projet GROUP_CONCAT sur Github, je pense que je fais exactement ce que vous recherchez:

ce projet contient un ensemble de fonctions agrégées SQLCLR définies par l'utilisateur (UDAs SQLCLR) qui offrent collectivement des fonctionnalités similaires à la fonction MySQL GROUP_CONCAT. Il y a plusieurs fonctions pour assurer la meilleure performance basée sur la fonctionnalité requise...

28
répondu MaxiWheat 2018-06-26 13:40:31

avec le code ci-dessous, vous devez définir PermissionLevel=External sur les propriétés de votre projet avant de déployer, et modifier la base de données pour faire confiance au code externe (assurez-vous de lire ailleurs sur les risques de sécurité et les alternatives [comme les certificats]) en lançant"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é ceci en utilisant une requête qui ressemble à:

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 rendements: A, B, C, D

6
répondu GregTSmith 2010-01-27 18:37:45

essayé ces, mais pour mes fins dans MS SQL Server 2005, ce qui suit a été le plus utile, que j'ai trouvé à xaprb

declare @result varchar(8000);

set @result = '';

select @result = @result + name + ' '

from master.dbo.systypes;

select rtrim(@result);

@Mark comme vous l'avez mentionné, c'est le caractère d'espace qui m'a causé des problèmes.

6
répondu isoughtajam 2011-05-04 11:26:40

pour concaténer tous les noms des directeurs de projet des projets qui ont plusieurs directeurs de projet écrire:

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
6
répondu Cmaly 2013-02-11 14:43:19

à propos de la réponse de J Hardiman, Que diriez-vous de:

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

soit dit en passant, est-ce que l'utilisation de" nom " est une faute de frappe ou est-ce que je ne comprends pas un concept ici?

en tout cas, merci beaucoup les gars parce que ça m'a fait gagner du temps:)

4
répondu user422190 2010-08-16 21:16:02

cela est facilement réalisable dans SQL Server 2017 avec STRING_AGG et WITHIN GROUP comme mentionné par Martin Smith . Vous pouvez également spécifier l'ordre des résultats concaténés en utilisant la clause WITHIN GROUP .

découvrez les documentation .

Voici comment vous feriez pour la question.

SELECT
    empName,
    STRING_AGG(projID, '/') WITHIN GROUP (ORDER BY projID) AS projIDs
FROM
    project_members 
GROUP BY 
    empName;
0
répondu Saugat Acharya 2018-10-01 12:03:26