¿Simular la función MySQL group_concat en Microsoft SQL Server 2005?

group contact sql

Question

Estoy intentando migrar una aplicación basada en MySQL a Microsoft SQL Server 2005 (no por elección, pero eso es vida).

En la aplicación original, utilizamos casi en su totalidad sentencias compatibles con ANSI-SQL, con una excepción significativa: usamos la función group_concat de MySQL con bastante frecuencia.

group_concat , por cierto, hace esto: dada una tabla de, digamos, nombres de empleados y proyectos ...

SELECT empName, projID FROM project_members;

devoluciones:

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

... y esto es lo que obtienes con group_concat:

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

devoluciones:

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

Entonces, lo que me gustaría saber es: ¿es posible escribir, digamos, una función definida por el usuario en SQL Server que emule la funcionalidad de group_concat ?

Casi no tengo experiencia en el uso de UDF, procedimientos almacenados ni nada de eso, solo SQL directo, así que, por favor, fíjate en el lado de demasiadas explicaciones :)

Answer 1

Con el siguiente código, debe establecer PermissionLevel = External en las propiedades de su proyecto antes de implementar, y cambiar la base de datos para que confíe en el código externo (asegúrese de leer en otro lugar sobre los riesgos de seguridad y alternativas [certificados similares]) ejecutando "ALTER DATABASE database_name SET CONFIANZA EN ".

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

He probado esto utilizando una consulta que se parece 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 

Y rendimientos: A, B, C, D.

Answer 2

Echa un vistazo al proyecto GROUP_CONCAT en Github, creo que hago exactamente lo que estás buscando:

Este proyecto contiene un conjunto de funciones agregadas definidas por el usuario SQLCLR (SQLCLR UDA) que ofrecen colectivamente una funcionalidad similar a la función MySQL GROUP_CONCAT. Existen múltiples funciones para garantizar el mejor rendimiento en función de la funcionalidad requerida ...

Answer 3

Intenté esto, pero para mis propósitos en MS SQL Server 2005, lo siguiente fue lo más útil, que encontré en xaprb

declare @result varchar(8000);

set @result = '''';

select @result = @result + name + '' ''

from master.dbo.systypes;

select rtrim(@result);

@Mark, como mencionaste, fue el carácter de espacio que me causó problemas.

Answer 4

No hay una manera REAL real de hacer esto. Aunque hay muchas ideas por ahí.

La mejor que he encontrado :

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 versión que funciona correctamente si los datos pueden contener caracteres como <

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 5

Para concatenar todos los nombres de los gerentes de proyecto de los proyectos que tienen múltiples gerentes de proyecto, escriba:

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

Answer 6

Posiblemente sea demasiado tarde para ser beneficioso ahora, pero ¿no es esta la forma más fácil de hacer las cosas?

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 7

Puede que llegue un poco tarde a la fiesta, pero este método funciona para mí y es más fácil que el método COALESCE.

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

Answer 8

Sobre la respuesta de J Hardiman, ¿qué tal?

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

Por cierto, ¿es el uso de "Apellidos" un error tipográfico o no estoy entendiendo un concepto aquí?

De todos modos, muchas gracias chicos porque me ahorraron bastante tiempo :)

Answer 9

SQL Server 2017 introduce una nueva función agregada

STRING_AGG ( expression, separator) .

Concatena los valores de las expresiones de cadena y coloca valores separadores entre ellas. El separador no se agrega al final de la cadena.

Los elementos concatenados se pueden ordenar agregando WITHIN GROUP (ORDER BY some_expression)

Para las versiones 2005-2016 , normalmente uso el método XML en la respuesta aceptada.

Esto puede fallar en algunas circunstancias sin embargo. por ejemplo, si los datos a concatenar contienen CHAR(29) , verá

FOR XML no pudo serializar los datos ... porque contiene un carácter (0x001D) que no está permitido en XML.

Un método más robusto que puede tratar con todos los caracteres sería usar un agregado CLR. Sin embargo, aplicar un ordenamiento a los elementos concatenados es más difícil con este enfoque.

El método de asignación a una variable no está garantizado y debe evitarse en el código de producción.