Jeff Moden (3/5/2011)

---

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.

SQLkiwi (3/7/2011)[hrWell 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.

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:

Jeff Moden (3/7/2011)

---

At the risk of quibbling, yeah... it's RBAR because it requires a scalar UDF (or equivalent) to return just one text aggregate and it handles the same data over and over until the full string is constructed. Call it what you want, though, because no matter what you and I call it, we both agree that it's also called "fairly slow".

SQLkiwi (3/7/2011)

---

I think I see what you mean, but we're talking about the "SELECT @fruit = COALESCE(@fruit + '', '') + name FROM table" method aren't we? There's no UDF or explicit loop there...or am I missing something? Quite possible, it is 4:40am!

jim Etheridge (3/7/2011)

---

This works to simplify the 'for xml path ('')' option: {snip]
The rtrim/ltrim function apparently removes the unprintable xml tokens, and the substring removes the first comma. This works consistently, where used without the rtrim/ltrim is spotty, as the author suggests.