ksatpute123 (7/24/2013)
Easiest wayDECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product
GROUP BY Name
SELECT @listStr
🙂
Yep, does look like. However...
Correct concatenation of string values (eg. accumulating of numeric values) using "SELECT @var = @var + Col FROM Table" costruction is not guaranteed in SQL Server!
Most of times, simple select will work. Having GROUP BY (as well as using JOINS) increases probability that accumulated value will not be correct. Selecting from sub-query with DISTINCT could be a bit safer option.
One of the reasons why all of the above ways may produce wrong results - query parallelising.
Could be some more reasons there too, that why OPTION (MAXDOP 1) may still not help...
I'm aware of only two ways of doing it safely with strings:
1. aggregate CLR function
2. using FOR XML PATH