---

How to concatenate varchar values from different records

A method to concatenate varchar values without using cursors or loops. With this method it is possible to insert a value separator too.

In the table below we want to concatenate every first name of my friends (comma separated)

Table Anagrafiche

IDAnag	FirstName	Surname	age
1	Mauro	Rossi	34
2	Simone	Casara	34
4	Alessandro	Chiappin	30
6	Alessio	Trevisan	31

DECLARE @Concat as varchar(8000) SELECT @Concat = isnull(@Concat + ', ', '') + ltrim(rtrim(FirstName)) FROM Anagrafiche -- result SELECT @Concat

The core of the method is in the isnull(@Concat + ', ', '') statement. When the statement concatenates the first record it finds @Concat value = null: the separator becomes null too. From the following record, @Concat is not null so the separator can be appended.

Watch out for the values that have to be appended. If there is the risk to find a null value, filter the resultset with "WHERE FirstName IS Not null" or use the isnull operator even on the second member of the statement: "ltrim(rtrim(isnull(FirstName,'')))"