SQL Server – Elegant Way for Concatenation

concatsql server

I am looking for the most elegant solution to prevent the addition of the last/ final separator ';' after the last row.

For example the result should be

'Peter,Paul,Jill'

instead of

'Peter,Paul,Jill,'

DECLARE @p varchar(max) = '';
SELECT @P = @P + col2 +',' FROM tab1 WHERE coli = 1
RETURN @P

The solution 

RETURN LEFT(@P, LEN(@P) -1);

works only if @P is long enough and so is not a solution…

How can I solve this elegant and reliable?

Best Answer

Without stuff function, we can also try the isnull trick (reusing code already done by Scott): 

Declare @Table table (StringText varchar(100))
DECLARE @ConcatString varchar(100)

insert into @Table values('Tom'), ('Dick'), ('Harry')

SELECT @ConcatString = isnull(@ConcatString + ',', '') + StringText
from @Table

print @ConcatString
Related Question