Gus already explained the STUFF trick to take away the leading comma, so I will try to explain the XML PATH trick. Not because I am an XML Guru, but rather because I just recently spent some considerable time trying to understand it myself:

The SELECT...FOR XML PATH feature is intended to allow you to easily build XML. The basic application on our sample data (from Lynn's example) would be:

Select ColB

From @TestTab

For XML PATHWhich produces:

Now this does technically concatenate our strings together, however, you will notice two problems with it:

1) the {row} tags, and

2) the {ColB} tags

(the line breaks and indentations were added by me for easy reading).

The {row} tags are intended to reflect the source table's name, but they can be overridden by the PATH argument. Using PATH('TableVar') would give us {TableVar} tags instead. As it happens a specific feature of FOR XML PATH is that if you use an explicitly empty name: PATH(''), instead of giving us empty row tags ({}...{/}) which would be invalid XML, it removes the row tags altogether. So:

Select ColB

From @TestTab

For XML PATH('')

This gives us the following:

So we still have the column tags to deal with.

Now it is clear the the column tags are coming from the column name, so you might think that all we have to do is give the column a blank name and the column tags will disappear also. And yes, that is true, but that is harder than it sounds because there is no way to explicitly give a column a blank name.

For instance, "SELECT ColB as [] From @TestTab" is invalid syntax. However, it turns out that there is an implicit way to do this: output columns that are expressions have no column name unless you explicitly give them one.

Thus, we can eliminate our column names by using some null operation on our column, like so:Select ''+ ColB

From @TestTab

For XML PATH('')which gives us:

Now all that's left is to add in the comma separator. In a happy coincidence, this merges perfectly with the previous trick, like so:Select ','+ ColB

From @TestTab

For XML PATH('')which finally gives us:

And this takes us back to the STUFF trick to get rid of the leading comma, as explained by Gus.

(edit: typos)