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
2. Dermatology
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 STUFF(((
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('.[1]', 'varchar(max)')
), 1, 1, '')
After adding "TYPE", we are able to get the expected output: Gynecology & Obstetrics, Dermatology
Reference : Tejas Shah (http://www.SQLYoga.com)
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
2. Dermatology
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 STUFF(((
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('.[1]', 'varchar(max)')
), 1, 1, '')
After adding "TYPE", we are able to get the expected output: Gynecology & Obstetrics, Dermatology
Reference : Tejas Shah (http://www.SQLYoga.com)
