Friday, September 14, 2007

Using COALESCE to Build Comma-Delimited String

This is one of my favorite little tricks to use in SQL Server programming. I don't have to use it very often, but it's definitely a nice way to build a delimited string, considering the other options available.
Like I said, I've only had to use this in 2 or maybe 3 places, but it definitely saved some time and definitely kept the code simple and clean!
Basically the old approach was to return a set of records and through some layer of code build a delimited list... Or if you were a bit more savvy you might have kept it all at the database level and used some type of cursor... But with COALESCE you can create the same results in a single SELECT statement.
The article shows some good examples of how to utilize this.