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

Advertisements
Concatenating items to a list in T-SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s