Elegant way for concatenation

Question

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?

Answer 1

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

Answer 2

I think a lot of people solve this with the STUFF function. I found an example from this link and adapted it.

SET NOCOUNT ON

DECLARE @Table TABLE (StringText VARCHAR(100))
DECLARE @ConcatString VARCHAR(100)

INSERT INTO @Table VALUES ('Tom')
INSERT INTO @Table VALUES ('Dick')
INSERT INTO @Table VALUES ('Harry')

SELECT @ConcatString = STUFF((
            SELECT ',' + StringText
            FROM @Table
            ORDER BY StringText
            FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')

PRINT @ConcatString

As Dan Guzman points out in the comments, specifying TYPE and value method avoids entity references in the resultant string for special characters like ampersand.