TSQL – How to select multiple rows into a single row

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:

Solution

Initially I tried to write one SELECT:

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. ‘