Jeff Moden (3/5/2011)
why dont we keep it simple?
SELECT @fruit = COALESCE(@fruit + '', '') + name
Because that's RBAR an solves only for one scalar return. Also, if the list is long, the code begins to slow down almost exponentially because it has to keep rebuilding the variable internally as well as growing the memory allocation for the variable which is also relatively expensive.
Well it's not really RBAR - it is a set-based solution of sorts, but you are absolutely right to point out that it is not at all suitable for a large number of elements, or an otherwise long result.
There are several other methods which are much better: the FOR XML PATH trick is the one most commonly employed. Yes, it may be a bit of a hack (it's an odd use of 'XML') and the contortions required to handle special characters like & can be annoying, and it won't work at all with some rare embedded characters, but even with all those caveats, it's still generally much to be preferred. Until Microsoft provide a well-performing built-in CONCAT function, it's one of the better options.
Another high-performance solution which has no character restrictions and a more intuitive syntax is based on SQLCLR. For very large strings, the RBAR .WRITE method is surprisingly worth considering. You can find test scripts and performance comparisons here: