341 votos

Simulando GROUP_CONCAT función MySQL en Microsoft SQL Server 2005?

Estoy tratando de migrar una base de datos MySQL basado en la aplicación a través de Microsoft SQL Server 2005 (no por elección, sino que es la vida).

En la aplicación original, hemos utilizado casi completamente ANSI-SQL compatible con las declaraciones, con una excepción importante -- se utilizó MySQL group_concat función con bastante frecuencia.

group_concat, por cierto, ¿esto: dada una tabla de, por ejemplo, los nombres de los empleados y de los proyectos de...

SELECT empName, projID FROM project_members;

devuelve:

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

... y aquí está lo que usted consigue con group_concat:

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

devuelve:

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

...Lo que me gustaría saber es: ¿Es posible escribir, decir, una función definida por el usuario en SQL Server que emula la funcionalidad de group_concat? Yo casi no tienen experiencia en el uso de Udf, procedimientos almacenados, ni nada parecido; simplemente recto-para arriba SQL -- así que por favor, es mejor errar por el lado de explicación demasiado :)

170voto

BradC Puntos 18833

De ninguna manera realmente fácil de hacer esto. Muchas ideas por ahí, sin embargo.

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;
 

167voto

Scott Puntos 491

Puede que sea 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, '')
 

49voto

J Hardiman Puntos 281

Posiblemente demasiado tarde para ser de beneficio, pero esto no es la manera 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
 

10voto

Cmaly Puntos 31

Para concatenar todos los nombres de los gestores de proyectos de proyectos que tienen múltiples gestores de proyectos de escritura:

 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
 

9voto

GregTSmith Puntos 21

Con el código de abajo tiene que establecer PermissionLevel = Externo sobre las propiedades del proyecto antes de implementar y cambiar la base de datos para confiar en código externo (asegúrese de leer en otros lugares acerca de los riesgos de seguridad y alternativas [como certificados]) mediante la ejecución de "ALTER DATABASE database_name SET CONFIANZA 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);
 }
    }
 

He probado esto con una consulta que se ve así:

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

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X