From SQL 2005, we are using FOR XML PATH('') for the string concatenation. I have also mentioned the same in very old blog: Generate Comma Separated List with SELECT statement
. Today, we face an issue when special character gets encoded and we hate to have special character in my string.
e.g: Table has records like:
1. Gynecology & Obstetrics
Expected output (after concatenating two rows): Gynecology & Obstetrics, Dermatology.
When we used FOR XML PATH
, we got the output as:
Gynecology & Obstetrics,Dermatology ("&" character is encoded and it lists out "&")
This output was not the one which we were expected. To get the expected output, we need to find workaround and we finally get it as:
SELECT ',' + RTRIM(CONVERT(VARCHAR(50), sm.Speciality))
FROM Table1 T2
INNER JOIN Table2 sm ON sm.ID = t2.Speciality_ID
FOR XML PATH(''),TYPE).value('.', 'varchar(max)')
), 1, 1, '')
After adding "TYPE", we are able to get the expected output: Gynecology & Obstetrics, Dermatology