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.