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:


Initially I tried to write one SELECT:

        SELECT ', ' + cast(id AS NVARCHAR(max)) + ': ' + name
        FROM People
        ORDER BY Id
        FOR XML PATH ('')

The problem here was FOR XML PATH escaped characters that could break the XML (e.g. ‘

Previous Post
Next Post