I only have the Express versions of MS SQL Server 2008 and Visual Studio. Given that I can't create a SQL Server project and therefore CLR solutions are out of the question, I've attempted to use
select col1, stuff( ( select ' ' + col2
from StrConcat t1
where t2.col1 = t1.col1
for xml path('')
),1,1,'')
from StrConcat t2
group by col1
order by col1
to get a row concatenated col2. col2 is a varchar field with some control characters like & and \n. When it is concatenated with the above SQL, it appears to escape those control characters ie. & becomes
& amp ; and \n becomes &#xOD, which is not what I want it to do. So, the question is, what black box magic is causing that to happen?
