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.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan