T-SQL: Convert Rows into delimited string

While working on .Net, SSRS or any other application, most of the time we came into a situation where we need table rows into a delimited string in sql output.

Let’s create a table with some data to illustrate this scenario.

Create table #Organization(Org_Id Int Identity,Org_Name varchar(100))

Go

Insert Into #Organization(Org_Name)Values

('AdventureWorks Cycle'),('North America Operations'),

('Northeast Division'),('Central Division'), ('France'),('USA Operations')

Go

Select Org_Id,Org_Name from #Organization

We have multiple ways to achieve the desire output. One of the way is by using
Coalesce() in-build function.

Declare @Output varchar(Max)

SELECT @Output=Coalesce(@Output+Org_Name+',',Org_Name+',') FROM #Organization

Select @Output as OutputValue

Another way is by using XML clause.

Declare @Output varchar(Max)

Set @Output=(SELECT Org_Name+',' FROM #Organization for XML path (''))

Select @Output as OutputValue

Point to remember during using this method is that if your column contains xml markup characters like “&”,”<”,”>” etc then you will get some unexpected output. In this sitution, you need to use Replace() method to get the expected output. To test this problem, add one more record in the #Organization table with value ‘North & South zone’ and test the above sql. In the output, you will notice “North &amp; South Zone” instead of “North & South Zone”

Last but not then least one is by using vairble in T-sql. Other ways are cursor, looping concept etc. that can be used depending on the requirement or sitution.

Declare @Output varchar(Max)

SELECT @Output=ISNULL(@Output,'')+Org_Name+',' FROM #Organization

Select @Output as OutputValue

Point to strongly remember is that NULL column values will result NULL or unexpected result in T-Sql output. So NULL must be handle before using using any of the approach.