Hugo Kornelis (12/24/2014)
DrKiller (12/23/2014)
wow what a complex way of doing it.I'm usually just doing this 🙂
DECLARE @tmpString VARCHAR(MAX)SELECT @tmpString = COALESCE(@tmpString + ', ' + productname, productname) FROM Header
SELECT @tmpString
Others already pointed out the performance issue. I have not yet seen warnings about this code being undocumented, unsupported, and unreliable. Though this code, based on empirical observation, usually works - it does not always. And when it doesn't, MS support will slam the door in your face and tell you that you're on your own. (Well, except when you're a multi-million customer...)
Not undocumented but rather poorly documented. Of course, I absolutely agree that it's totally unsupported and I've also seen it cause some very odd problems like it returning only the first value under certain conditions. Unfortunately, I've not preserved that particular test and I've lost track of the link that had a similar test that demonstrates the problem. I think I may have gotten the link from you in the past or it could just be ailing memory. Do you happen to have such a link?
{EDIT} Never mind. I found the link I was looking for. Here it is and it does actually show the single element return problem I spoke of.
http://sqlmag.com/sql-server/multi-row-variable-assignment-and-order
There are certainly work arounds for all of these problems but, as many will be quick to cite, they are all still unsupported and usually add complexity beyond that of the XML method even with its de-entitization problems.
--Jeff Moden
Change is inevitable... Change for the better is not.