Consulta SQL-Concatenar resultados en una cadena [duplicar]

Esta pregunta ya tiene una respuesta aquí:

Tengo una función sql que incluye este código: 

DECLARE @CodeNameString varchar(100)

SELECT CodeName FROM AccountCodes ORDER BY Sort

Necesito concatenar todos los resultados de la consulta select en CodeNameString.

Obviamente un bucle FOREACH en código C# haría esto, pero ¿cómo lo hago en SQL?

Author: OMG Ponies, 2011-03-04
Source

6 answers

Si estás en SQL Server 2005 o superior, puedes usar este FOR XML PATH & STUFF truco: 

DECLARE @CodeNameString varchar(100)

SELECT 
   @CodeNameString = STUFF( (SELECT ',' + CodeName 
                             FROM dbo.AccountCodes 
                             ORDER BY Sort
                             FOR XML PATH('')), 
                            1, 1, '')

El FOR XML PATH('') básicamente concatena tus cadenas en un resultado XML largo (algo así como ,code1,code2,code3, etc.) y el STUFF pone un carácter " nada "en el primer carácter, por ejemplo, borra la primera coma" superflua", para darle el resultado que probablemente está buscando.

ACTUALIZAR: OK-Entiendo los comentarios-si su texto en la tabla de la base de datos ya contiene caracteres like <, > o &, entonces mi solución actual de hecho codificará esos en &lt;, &gt;, y &amp;.

Si tiene un problema con esa codificación XML, entonces sí, debe mirar la solución propuesta por @KM que también funciona para esos caracteres. Una palabra de advertencia de mi parte: este enfoque es mucho más intensivo en recursos y procesamiento, solo para que lo sepas.

 74
Author: marc_s,
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-30 14:12:02 
DECLARE @CodeNameString varchar(max)
SET @CodeNameString=''

SELECT @CodeNameString=@CodeNameString+CodeName FROM AccountCodes ORDER BY Sort
SELECT @CodeNameString
 24
Author: AlexanderMP,
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-03-04 16:18:31

La respuesta de@AlexanderMP es correcta, pero también puedes considerar manejar los nulos con coalesce: 

declare @CodeNameString  nvarchar(max)
set @CodeNameString = null
SELECT @CodeNameString = Coalesce(@CodeNameString + ', ', '') + cast(CodeName as varchar) from AccountCodes  
select @CodeNameString
 10
Author: James Wiseman,
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-03-04 16:22:26

Para SQL Server 2005 y superior use Coalesce para nulls y estoy usando Cast o Convert si hay numeric values - 

declare @CodeNameString  nvarchar(max)
select  @CodeNameString = COALESCE(@CodeNameString + ',', '')  + Cast(CodeName as varchar) from AccountCodes  ORDER BY Sort
select  @CodeNameString
 4
Author: Vishal,
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-03-04 16:29:54

Desde msdn No utilice una variable en una instrucción SELECT para concatenar valores (es decir, para calcular valores agregados). Pueden producirse resultados de consulta inesperados. Esto se debe a que no se garantiza que todas las expresiones de la lista de SELECCIÓN (incluidas las asignaciones) se ejecuten exactamente una vez para cada fila de salida

Lo anterior parece decir que la concatenación como se hizo anteriormente no es válida, ya que la asignación podría realizarse más veces que las filas devueltas por el select

 2
Author: peter,
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-11-25 16:46:54

Aquí hay otro ejemplo de la vida real que funciona bien al menos con la versión de 2008 (y posteriores).

Esta es la consulta original que usa simple max() para obtener al menos uno de los valores:

SELECT option_name, Field_M3_name, max(Option_value) AS "Option value", max(Sorting) AS "Sorted"
FROM Value_list group by Option_name, Field_M3_name
ORDER BY option_name, Field_M3_name

Versión mejorada, donde la principal mejora es que mostramos todos los valores separados por comas: 

SELECT from1.keys, from1.option_name, from1.Field_M3_name,

 Stuff((SELECT DISTINCT ', ' + [Option_value] FROM Value_list from2
  WHERE COALESCE(from2.Option_name,'') + '|' + COALESCE(from2.Field_M3_name,'') = from1.keys FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'') AS "Option values",

 Stuff((SELECT DISTINCT ', ' + CAST([Sorting] AS VARCHAR) FROM Value_list from2
  WHERE COALESCE(from2.Option_name,'') + '|' + COALESCE(from2.Field_M3_name,'') = from1.keys FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'') AS "Sorting"

FROM ((SELECT DISTINCT COALESCE(Option_name,'') + '|' + COALESCE(Field_M3_name,'') AS keys, Option_name, Field_M3_name FROM Value_list)
-- WHERE
) from1
ORDER BY keys

Tenga en cuenta que hemos resuelto todos los posibles problemas de casos NULL que se me ocurren y también hemos corregido un error que obtuvimos para los valores numéricos (ordenación de campos).

 0
Author: Timo Riikonen,
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-07-11 10:39:45