Converting column values to comma delimited list (csv)

Once again I recently met the challenge of how to convert a list of rows to one comma delimited field. (And NOT use cursors)

Eg.
ColumnToCsv
Once
upon
a
time

Converted to Once,upon,a,time

The solution surprisingly enough lies in the build in sql function COALESCE.

Declare @CSVField varchar(100)
Select @CSVField = COALESCE(@CSVField + ',','') + ColumnToCsv
From SomeTable

Select @CSVField

Result
**********************
Once,upon,a,time

The Coalesce function returns the first not null value in the list of expressions. Which in this case is the ColumnToCsv value. And in this case it does it for each row in the select resultset.