175 votos

¿Cómo usar GROUP BY para concatenar cadenas en SQL Server?

¿Cómo obtengo:

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

Para

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

331voto

Kevin Fairchild Puntos 5991

No CURSOR, mientras lazo, o por el usuario función necesaria.

Sólo necesito ser creativo con FOR XML y trayectoria.

[Nota: esta solución sólo funciona en SQL 2005 y posteriores. Pregunta original no ha especificado 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

32voto

Allen Puntos 320

el uso de XML path no perfectamente concatenar como se podría esperar... que reemplazará a "&" con "&amp;" y también meterse con <" and "> ...tal vez un par de otras cosas, no estoy seguro...pero puedes probar con este

Encontré una solución para esto... se necesita 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 tienen un concatenar función de agregado? este es un pan de PITA.

21voto

Jonathan Sayce Puntos 1942

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

La última versión de mi código (que no responde a la pregunta original, pero puede ser útil a alguien) se parece a esto:

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 la sustitución de todos los espacios con comas, sólo pre-pends una coma y un espacio para cada valor, a continuación, utiliza STUFF a quitar los dos primeros caracteres.

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

7voto

Tom H. Puntos 23783

Para añadir a lo que dijo Cade, esto es generalmente una pantalla front-end y por lo tanto, debe ser manejado ahí. 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 "SQL sólo", pero la mayoría de las veces que esta concatenación debe ser manejado en su capa de pantalla.

4voto

Cade Roux Puntos 53870

Este tipo de pregunta se pide aquí muy a menudo, y la solución va a depender mucho de los requerimientos subyacentes:

http://stackoverflow.com/search?q=SQL+Pivot

y

http://stackoverflow.com/search?q=SQL+Concatenate

Normalmente, es imposible hacerlo sin sql dinámico, una función definida por el usuario o un cursor SQL sólo.

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