396 votos

¿Cómo utilizar GROUP BY para concatenar cadenas en Sql Server?

¿Cómo lo consigo?

id       Name       Value
1          A          4
1          B          8
2          C          9

a

id          Column
1          A:4, B:8
2          C:9

18 votos

Este tipo de problema se resuelve fácilmente en MySQL con su GROUP_CONCAT() función agregada, pero resolverlo en Microsoft Sql Server es más complicado. Consulte la siguiente pregunta del SO para obtener ayuda: " ¿Cómo obtener varios registros contra un registro basado en una relación? "

1 votos

Todos los que tienen una cuenta de microsoft deberían votar por una solución más sencilla en connect: connect.microsoft.com/SQLServer/feedback/details/427987/

1 votos

Puede utilizar los agregados SQLCLR que se encuentran aquí como sustituto hasta que se mejore el T-SQL: groupconcat.codeplex.com

580voto

Kevin Fairchild Puntos 5991

No se necesita un CURSOR, un bucle WHILE o una función definida por el usuario .

Sólo hay que ser creativo con FOR XML y PATH.

[Nota: Esta solución sólo funciona en SQL 2005 y posteriores. La pregunta original no especificaba la versión en uso].

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
  [ID],
  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

6 votos

¿por qué habría de nolizarse una tabla temporal?

3 votos

Esto es lo más genial de SQL que he visto en mi vida. ¿Alguna idea de si es "rápido" para grandes conjuntos de datos? No empieza a arrastrarse como lo haría un cursor o algo así, ¿no? Me gustaría que más gente votara esta locura.

1 votos

@mbrierst, este ejemplo no se hizo pensando en la eficiencia. Principalmente quería mostrar una solución alternativa. Cuando preguntas qué tan rápido es para conjuntos de datos grandes, ¿cuál es tu definición de "grande" y "rápido"? Estoy seguro de que hay una forma más elegante de reescribirlo con mejor eficiencia, sin embargo.

51voto

Allen Puntos 320

Utilizando la ruta XML no concatenará perfectamente como se podría esperar... sustituirá "&" por "&" y también se meterá con <" and "> ...tal vez algunas otras cosas, no estoy seguro... pero puedes probar esto

Me encontré con una solución para esto ... usted necesita para reemplazar:

FOR XML PATH('')
)

con:

FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(MAX)')

...o NVARCHAR(MAX) si eso es lo que estás usando.

por qué diablos no SQL ¿tiene una función de concatenación de agregados? esto es un PITA.

2 votos

He recorrido la red buscando la mejor manera de NO codificar la salida. ¡Muchas gracias! Esta es la respuesta definitiva - hasta que MS añada un soporte adecuado para esto, como una función agregada CONCAT(). Lo que hago es lanzar esto en un Outer-Apply que devuelve mi campo concatenado. No me gusta añadir selecciones anidadas en mis declaraciones de selección.

0 votos

Estoy de acuerdo, sin usar Value, podemos encontrarnos con problemas cuando el texto es un carácter codificado en XML. Por favor, encontrar mi blog que cubre los escenarios para la concatenación agrupada en Sql Server. blog.vcillusion.co.in/

42voto

Jonathan Sayce Puntos 1942

Me encontré con un par de problemas cuando intenté convertir la sugerencia de Kevin Fairchild para que funcionara con cadenas que contienen espacios y caracteres especiales XML ( & , < , > ) que fueron codificados.

La versión final de mi código (que no responde a la pregunta original pero puede ser útil para alguien) tiene el siguiente aspecto:

CREATE TABLE #YourTable ([ID] INT, [Name] VARCHAR(MAX), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'Oranges & Lemons',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'1 < 2',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT  [ID],
  STUFF((
    SELECT ', ' + CAST([Name] AS VARCHAR(MAX))
    FROM #YourTable WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE 
     /* Use .value to uncomment XML entities e.g. &gt; &lt; etc*/
    ).value('.','VARCHAR(MAX)') 
  ,1,2,'') as NameValues
FROM    #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

En lugar de utilizar un espacio como delimitador y sustituir todos los espacios por comas, simplemente añade una coma y un espacio a cada valor y luego utiliza STUFF para eliminar los dos primeros caracteres.

La codificación XML se realiza automáticamente mediante el uso de la función TIPO directiva.

7voto

Tom H. Puntos 23783

Sólo para añadir a lo que ha dicho Cade, esto suele ser una cosa de visualización del front-end y por lo tanto debe ser manejado allí. Sé que a veces es más fácil escribir algo 100% en SQL para cosas como la exportación de archivos u otras soluciones "sólo SQL", pero la mayoría de las veces esta concatenación debe ser manejada en su capa de visualización.

11 votos

¿Ahora la agrupación es una cosa de visualización frontal? Hay un montón de escenarios válidos para concatenar una columna en un conjunto de resultados agrupados.

7voto

Cade Roux Puntos 53870

Este tipo de preguntas se plantean aquí muy a menudo, y la solución va a depender mucho de los requisitos subyacentes:

https://stackoverflow.com/search?q=sql+pivote

y

https://stackoverflow.com/search?q=sql+concatenado

Normalmente, no hay ninguna forma de hacer esto sin sql dinámico, una función definida por el usuario o un cursor.

2 votos

No es cierto. la solución de cyberkiwi que utiliza cte:s es puro sql sin ningún tipo de hacking específico del proveedor.

1 votos

En el momento de la pregunta y la respuesta, yo no habría considerado los CTE recursivos como terriblemente portátiles, pero ahora son soportados por Oracle. La mejor solución va a depender de la plataforma. Para Sql Server lo más probable es que sea la técnica FOR XML o un agregado CLR del cliente.

1 votos

¿la respuesta definitiva a todas las preguntas? [stackoverflow.com/search?q=[whatever](http://stackoverflow.com/search?q=[whatever) la pregunta]

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