¿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
A
id Column
1 A:4, B:8
2 C:9
16 answers
No se necesita CURSOR, bucle WHILE o Función definida por el Usuario.
Solo hay que ser creativo con XML y PATH.
[Nota: Esta solución solo funciona en SQL 2005 y versiones posteriores. La pregunta original no especificó 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-07-29 19:15:38
El uso de XML path no se concatena perfectamente como es de esperar... reemplazará " & " por " & " y también se mezclará con <" and ">
...tal vez algunas otras cosas, no estoy seguro...pero puedes probar esto
Me encontré con una solución alternativa para esto... 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 tiene SQL
una función de agregado concatenado? esto es una PITA.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-04-24 04:09:34
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 fueron 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 usa STUFF
para eliminar los dos primeros caracteres.
La codificación XML se realiza automáticamente mediante la directiva TYPE.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-01-23 15:18:19
Si es SQL Server 2017 o SQL Server Vnext, SQL Azure puede usar string_agg de la siguiente manera:
select id, string_agg(concat(name, ':', [value]), ', ')
from #YourTable
group by id
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-04-27 17:32:28
Otra opción usando Sql Server 2005 y superior
---- test data
declare @t table (OUTPUTID int, SCHME varchar(10), DESCR varchar(10))
insert @t select 1125439 ,'CKT','Approved'
insert @t select 1125439 ,'RENO','Approved'
insert @t select 1134691 ,'CKT','Approved'
insert @t select 1134691 ,'RENO','Approved'
insert @t select 1134691 ,'pn','Approved'
---- actual query
;with cte(outputid,combined,rn)
as
(
select outputid, SCHME + ' ('+DESCR+')', rn=ROW_NUMBER() over (PARTITION by outputid order by schme, descr)
from @t
)
,cte2(outputid,finalstatus,rn)
as
(
select OUTPUTID, convert(varchar(max),combined), 1 from cte where rn=1
union all
select cte2.outputid, convert(varchar(max),cte2.finalstatus+', '+cte.combined), cte2.rn+1
from cte2
inner join cte on cte.OUTPUTID = cte2.outputid and cte.rn=cte2.rn+1
)
select outputid, MAX(finalstatus) from cte2 group by outputid
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-06-10 10:31:45
Instale los agregados SQLCLR desde http://groupconcat.codeplex.com
Entonces puedes escribir código como este para obtener el resultado que pediste:
CREATE TABLE foo
(
id INT,
name CHAR(1),
Value CHAR(1)
);
INSERT INTO dbo.foo
(id, name, Value)
VALUES (1, 'A', '4'),
(1, 'B', '8'),
(2, 'C', '9');
SELECT id,
dbo.GROUP_CONCAT(name + ':' + Value) AS [Column]
FROM dbo.foo
GROUP BY id;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-03-19 03:40:12
SQL Server 2005 y posteriores le permiten crear sus propias funciones agregadas personalizadas, incluso para cosas como la concatenación: vea el ejemplo en la parte inferior del artículo vinculado.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-11-20 04:11:23
Ocho años después... Microsoft SQL Server vNext Database Engine finalmente ha mejorado Transact-SQL para admitir directamente la concatenación de cadenas agrupadas. La versión 1.0 de Community Technical Preview agregó la función STRING_AGG y CTP 1.1 agregó la cláusula WITHIN GROUP para la función STRING_AGG.
Referencia: https://msdn.microsoft.com/en-us/library/mt775028.aspx
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-02-10 21:03:57
Solo para agregar a lo que dijo Cade, esto suele ser una pantalla de front-end y, por lo tanto, debe manejarse 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 "solo SQL", pero la mayoría de las veces esta concatenación debe manejarse en su capa de visualización.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-03-06 14:42:04
En Oracle puede utilizar la función de agregado LISTAGG. Un ejemplo sería:
name type
------------
name1 type1
name2 type2
name2 type3
SELECT name, LISTAGG(type, '; ') WITHIN GROUP(ORDER BY name)
FROM table
GROUP BY name
Resultaría en:
name type
------------
name1 type1
name2 type2; type3
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-02-15 10:55:52
Este tipo de pregunta se hace 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 + concatenar
Normalmente, no hay una forma de hacerlo solo con SQL sin sql dinámico, una función definida por el usuario o un cursor.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 11:55:01
Esto es solo una adición al post de Kevin Fairchild (muy inteligente por cierto). Lo habría añadido como un comentario, pero no tengo suficientes puntos todavía:)
Estaba usando esta idea para una vista en la que estaba trabajando, sin embargo, los elementos que estaba concatinando contenían espacios. Así que modifiqué el código ligeramente para no usar espacios como delimitadores.
De nuevo gracias por la solución cool Kevin!
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],
REPLACE(REPLACE(REPLACE(
(SELECT [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) as A
FROM #YourTable
WHERE ( ID = Results.ID )
FOR XML PATH (''))
, '</A><A>', ', ')
,'<A>','')
,'</A>','') AS NameValues
FROM #YourTable Results
GROUP BY ID
DROP TABLE #YourTable
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-05-09 16:12:18
No necesita un cursor... un bucle while es suficiente.
------------------------------
-- Setup
------------------------------
DECLARE @Source TABLE
(
id int,
Name varchar(30),
Value int
)
DECLARE @Target TABLE
(
id int,
Result varchar(max)
)
INSERT INTO @Source(id, Name, Value) SELECT 1, 'A', 4
INSERT INTO @Source(id, Name, Value) SELECT 1, 'B', 8
INSERT INTO @Source(id, Name, Value) SELECT 2, 'C', 9
------------------------------
-- Technique
------------------------------
INSERT INTO @Target (id)
SELECT id
FROM @Source
GROUP BY id
DECLARE @id int, @Result varchar(max)
SET @id = (SELECT MIN(id) FROM @Target)
WHILE @id is not null
BEGIN
SET @Result = null
SELECT @Result =
CASE
WHEN @Result is null
THEN ''
ELSE @Result + ', '
END + s.Name + ':' + convert(varchar(30),s.Value)
FROM @Source s
WHERE id = @id
UPDATE @Target
SET Result = @Result
WHERE id = @id
SET @id = (SELECT MIN(id) FROM @Target WHERE @id < id)
END
SELECT *
FROM @Target
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-11-07 19:29:26
Vamos a ser muy simples:
SELECT stuff(
(
select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb
FOR XML PATH('')
)
, 1, 2, '')
Reemplace esta línea:
select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb
Con su consulta.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-09-22 11:56:39
No vio ninguna respuesta de aplicación cruzada, tampoco hubo necesidad de extracción xml. Aquí hay una versión ligeramente diferente de lo que escribió Kevin Fairchild. Es más rápido y fácil de usar en consultas más complejas:
select T.ID
,MAX(X.cl) NameValues
from #YourTable T
CROSS APPLY
(select STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
FROM #YourTable
WHERE (ID = T.ID)
FOR XML PATH(''))
,1,2,'') [cl]) X
GROUP BY T.ID
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-03-15 10:29:16
Puede mejorar el rendimiento significativo de la siguiente manera si el grupo by contiene principalmente un elemento:
SELECT
[ID],
CASE WHEN MAX( [Name]) = MIN( [Name]) THEN
MAX( [Name]) NameValues
ELSE
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
END
FROM #YourTable Results
GROUP BY ID
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-06-23 12:58:33