Creating a Delimited List in SQL Server without Cursors or Looping Using Coalesce or IsNull

Suppose you wanted to return a list of names from a Customer table in a database in a comma delimited list. You could create a SQL Query and loop through the rows with a cursor or you could return the result set and loop through it your application code, but there is another alternative. You can use this clever bit of SQL code:

declare @MyList varchar(1000)


SELECT @MyList = Coalesce(@MyList + ', ', '') + field_name

from table_name

print @MyList
You can also substitute IsNull for Coalesce when I first heard about this technique Coalesce was used, but IsNull seems to work just as well. The reason it works is because as the select takes place it appends the field value to the variable for each row in the result set. The IsNull or Coalesce will keep it from putting a delimiter in front of the first item