SQL Server 2017 does introduce a new aggregate function
STRING_AGG ( expression, separator).
Concatenates the values of string expressions and places separator
values between them. The separator is not added at the end of string.
The concatenated elements can be ordered by appending WITHIN GROUP (ORDER BY some_expression)
For versions 2005-2016 I typically use the XML method in the accepted answer.
This can fail in some circumstances however. e.g. if the data to be concatenated contains CHAR(29) you see
FOR XML could not serialize the data … because it
contains a character (0x001D) which is not allowed in XML.
A more robust method that can deal with all characters would be to use a CLR aggregate. However applying an ordering to the concatenated elements is more difficult with this approach.
The method of assigning to a variable is not guaranteed and should be avoided in production code.