Consulta SQL – Concatenando Resultados em uma String

Eu tenho uma function sql que inclui este código:

DECLARE @CodeNameString varchar(100) SELECT CodeName FROM AccountCodes ORDER BY Sort 

Preciso concatenar todos os resultados da consulta de seleção no CodeNameString.

Obviamente, um loop FOREACH no código C # faria isso, mas como faço isso em SQL?

Se você está no SQL Server 2005 ou acima, você pode usar este truque FOR XML PATH & STUFF :

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

O FOR XML PATH('') basicamente concatena suas strings em um único resultado XML longo (algo como ,code1,code2,code3 etc.) e o STUFF coloca um caractere “nothing” no primeiro caractere, por exemplo, apaga o caractere ” primeira vírgula supérflua, para dar o resultado que você provavelmente está procurando.

UPDATE: OK – Eu entendo os comentários – se o seu texto na tabela do database já contém caracteres como < , > ou & , então minha solução atual irá de fato codificá-los em < > e & .

Se você tem um problema com essa codificação XML - então sim, você deve olhar para a solução proposta pelo @KM que também funciona para esses caracteres. Uma palavra de advertência minha: essa abordagem exige muito mais resources e processamento - só para você saber.

 DECLARE @CodeNameString varchar(max) SET @CodeNameString='' SELECT @CodeNameString=@CodeNameString+CodeName FROM AccountCodes ORDER BY Sort SELECT @CodeNameString 

@ Resposta do AlexanderMP está correta, mas você também pode considerar o tratamento de nulos com coalesce :

 declare @CodeNameString nvarchar(max) set @CodeNameString = null SELECT @CodeNameString = Coalesce(@CodeNameString + ', ', '') + cast(CodeName as varchar) from AccountCodes select @CodeNameString 

Para o SQL Server 2005 e superior, use Coalesce para nulls e estou usando o Cast ou o Convert se houver numeric values

 declare @CodeNameString nvarchar(max) select @CodeNameString = COALESCE(@CodeNameString + ',', '') + Cast(CodeName as varchar) from AccountCodes ORDER BY Sort select @CodeNameString 

from msdn Não use uma variável em uma instrução SELECT para concatenar valores (isto é, para calcular valores agregados). Resultados de consulta inesperados podem ocorrer. Isso ocorre porque todas as expressões na lista SELECT (incluindo atribuições) não têm garantia de serem executadas exatamente uma vez para cada linha de saída

O acima parece dizer que a concatenação como feita acima não é válida, pois a atribuição pode ser feita mais vezes do que as linhas retornadas pelo select.

Aqui está outro exemplo da vida real que funciona bem, pelo menos, com o lançamento de 2008 (e mais tarde).

Esta é a consulta original que usa o simples max() para obter pelo menos um dos 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 

Versão aprimorada, em que a principal melhoria é que mostramos todos os valores separados por vírgula:

 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 

Observe que resolvemos todos os possíveis problemas de caso NULL em que eu posso pensar e também corrigimos um erro que recebemos para valores numéricos (campo Classificação).