Press "Enter" to skip to content

Translating a Result Set into a Comma-Separated List

Kiana Bergsma shows us a tried-and-true method to confuse people:

Often times I have told developers, here is how you do it, and if you Google on it you will find some great samples.  Now it is time that I provide my own sample. I call this the FOR XML hack since it used the FOR XML command, without actually involving any XML at all.

I’m quite happy that STRING_AGG() is around as of SQL Server 2017, as it is a much clearer representation of how to solve this problem. If I had a dollar for every time somebody needed me to explain why I used FOR XML PATH() when I clearly wasn’t building XML, I’d have several dollars. Probably not a fistful of dollars, though.