You can avoid the XML characters problem in the For XML Path version by using the value() method on the XML.
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T;
FROM ( VALUES ( '1>'), ( '2&'), ( '3/') ) AS TCV (Col);
SELECT STUFF((SELECT ',' + Col
ORDER BY Col DESC
FOR XML PATH(''),
TYPE).value('.', 'nvarchar(max)'), 1, 1, '');
You can use either varchar or nvarchar in the value() method. Either works. I usually use nvarchar because the company I work for is multinational and has to deal with extended character set data.
The Concat/IsNull method doesn't work with Order By, is prone to string truncation, breaks if the query goes parallel (multiple threads), and is subject to change without notice if MS does work on the query engine. The XML version has none of those weaknesses. If you're using a version of SQL Server that doesn't support For XML (pre-SQL 2000), then use a cursor, not the Concat method, unless you want the code to sometimes work and sometimes not.
Note: My sample table is built using an SQL 2008+ feature, Table Value Constructor. If you want to test the final query in SQL 2005 or SQL 2000, build the sample data using Union All instead.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon