Creating concatenated delimited string from a SQL result set and avoid character encoding when using “FOR XML PATH”

There are a number of ways of getting a column result set into a single delimited string. If it is a function then you can use single variable assigning method or STUFF tsql function. The STUFF function in conjunction with FOR XML PATH code is the most easy usage if you have to use as an inner query. But it automatically html encodes string in certain cases (see case 2 bad example) .

Lets look at a simple exaple of  number of country names

Case 1. Variable based solution 

declare @delimitedCountryName varchar(max)

set @delimitedCountryName=''

select @delimitedCountryName+=case when len(@delimitedCountryName) > 0 then +','+ CountryName else CountryName end

FROM Country

select @delimitedCountryName

Case 2. The XML path and stuff system function based solution

stack overflow query on html encoding

//BAD: because if country name contains special characters then it will be automatically html encoded (e.g. if country name is 'papua & new guinea' it will be shown as    'papua & new guinea' 

select stuff(

(select ', ' + countryname

from Country

for xml path('')

)

, 1, 1, '') as delimitedCountryName;

// GOOD approach (either use case 1 or below option)

select stuff(

(select ', ' + countryname

from Country

for xml path(''), root('MyString'), type

).value('/MyString[1]','varchar(max)')

, 1, 2, '') as delimitedCountryName;

Read this simple blog for some info on why second approach is slightly better