This is one of my favorite little tricks in SQL Server. I do not need it often, but it is a clean way to build a delimited string compared to the alternatives.

The old approach was to return a set of records and build the delimited list in a layer of code. Or, if you wanted to keep it at the database level, to use a cursor. With COALESCE you can get the same result in a single SELECT:

DECLARE @List VARCHAR(MAX)

SELECT @List = COALESCE(@List + ', ', '') + Name
FROM   MyTable
ORDER BY Name

SELECT @List

The trick is what COALESCE does on the first row. @List starts out NULL, so @List + ', ' is also NULL, and COALESCE falls through to the empty string. That gives you the first value with no leading comma. On every row after that @List has a value, so the separator gets added normally.

I have only needed this in two or three places, but it saved time and kept the code simple each time.

One note from the present day: SQL Server 2017 added STRING_AGG, which does this directly and handles the separator for you:

SELECT STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name)
FROM   MyTable

On a modern instance that is the better choice. The COALESCE approach is still useful to understand, and still necessary if you are on anything older.