¿Cómo utilizar GROUP BY para concatenar cadenas en SQL Server?
Como 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
No se necesita CURSOR, bucle WHILE ni función definida por el usuario .
Sólo hay que ser creativo con FOR XML y PATH.
[Nota: esta solución solo funciona en SQL 2005 y versiones 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
Si es SQL Server 2017 o SQL Server Vnext, puede usar SQL Azure STRING_AGGde la siguiente manera:
SELECT id, STRING_AGG(CONCAT(name, ':', [value]), ', ')
FROM #YourTable
GROUP BY id
el uso de la ruta XML no concatenará perfectamente como cabría esperar... reemplazará "&" por "&" y también interferirá con <" and ">
... tal vez algunas otras cosas, no estoy seguro... pero puedes probar esto
Encontré una solución para esto... necesitas 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 SQLtiene una función agregada concatenada? esto es un PITA.
Me encontré con un par de problemas cuando intenté convertir la sugerencia de Kevin Fairchild para trabajar con cadenas que contienen espacios y caracteres XML especiales ( ,, &) que estaban codificados.<>
La versión final de mi código (que no responde a la pregunta original pero puede ser útil para alguien) se ve así:
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. > < etc*/
).value('.','VARCHAR(MAX)')
,1,2,'') as NameValues
FROM #YourTable Results
GROUP BY ID
DROP TABLE #YourTable
En lugar de usar un espacio como delimitador y reemplazar todos los espacios con comas, simplemente antepone una coma y un espacio a cada valor y luego los usa STUFFpara eliminar los dos primeros caracteres.
La codificación XML se realiza automáticamente mediante la directiva TYPE .
