Concatenate Strings in SQL Server - Different ways

There are many ways to concatenate data in a single column. Consider the following table:



If you want to concatenate every row value separated by comma, use these methods:

1. Use Variable to Concatenate

declare @sql varchar(8000)
select @sql=coalesce(@sql+',','')+data
from @t
select @sql
In the above T-SQL code, each row value is concatenated with the variable @sql

2. Use FOR XML clause to Concatenate

select distinct
        stuff((select distinct top 100 percent ',' + data from @t as t for xml path('')), 1, 1, '')
from        @t as t

In the above example, FOR XML clause is used to concatenate row values. Since a comma is used, the query concatenates values separated by comma.