I spend too long on finding the solution for this problem…
Basically I had a table which looked like this
What I wanted was to select all as one row as a set of items separated with a comma, where an item was ‘id: name’. The items had to be sorted by the id. So the result was supposed to look as below:
Initially I tried to write one
SELECT STUFF ( ( SELECT ', ' + cast(id AS NVARCHAR(max)) + ': ' + name FROM People ORDER BY Id FOR XML PATH ('') ),1,1,'' )
The problem here was
FOR XML PATH escaped characters that could break the XML (e.g. ‘<' was replaced with '<'), which was not acceptible for me.
I looked for another solution but whatever I tried didn't work because of the
ORDER BY statement which can't be used in a subquery or inline view.
Finally I decided to use a temporary table filling it with correct data and then create the final result. Here is the code:
DECLARE @res NVARCHAR(max) SET @res = '' CREATE TABLE #Result ( item nvarchar(max) ) INSERT INTO #Result SELECT CAST(Id AS NVARCHAR(max)) + ': ' + Name AS item FROM People ORDER BY id SELECT @res = @res + item + ', ' from #Result SELECT substring(@res,1,len(@res)-1) as Result
If you find a better solution can you please post it as a comment? I could use in in future Thanks in advance!