How to concatenate all results of a query in SQL Server in order to return all information in a variable?
For example, considering a cliente table with the following composition:
╔═══╦════════════╗
║ ║ Nome ║
╠═══╬════════════╣
║ 1 ║ José ║
║ 2 ║ Luis ║
║ 3 ║ Antônio ║
╚═══╩════════════╝
The desired result would be:
José, Luis, Antonio
One way (prior to SQL 2012) is to use a variable to concatenate the row results as follows:
DECLARE @texto varchar(max);
SELECT @texto = ISNULL(@texto + ', ', '') + cli.nome
FROM cliente cli;
print @texto;
Where ISNULL guarantees that the comma will only be entered if there is a previous value assigned to the variable (In the case starting to put the comma just after José ).
Another way is to use the FOR XML PATH('') syntax:
SELECT DISTINCT SUBSTRING((SELECT ',' + cli.nome AS [text()]
FROM cliente cli
FOR XML PATH ('')), 2, 8000) AS texto
FROM cliente cli2
References:
Answer to the SQL, How to Concatenate results? question in SOen ;
Answer to the question Concatenate many rows into a single text string? no SOen;