Concatenating items to a list in T-SQL

Sometimes in SQL, you need the ability to concatenate rows of data together to form delimited lists. There are two ways this can be done: Using ISNULL and Stuff/XML Path. Below are examples of both. I typically use the ISNULL version more often since it’s feels simpler for me to remember:

Using ISNULL to create a list

declare @list varchar(max)
select top 10 @list = isnull(@list+';','')+FirstName
from AdventureWorks2014.Person.Person
print @list
-- Result:
Syed;Catherine;Kim;Kim;Kim;Hazem;Sam;Humberto;Gustavo;Pilar

Using STUFF/XML Path to create a list

declare @list_xml varchar(max);

set @list_xml = stuff((select top 10 ';'+FirstName
		from AdventureWorks2014.Person.Person
		for xml path ('')),1,1,'')
print @list_xml
-- Result:
Syed;Catherine;Kim;Kim;Kim;Hazem;Sam;Humberto;Gustavo;Pilar