Combining result strings in SQL Server

This is just a quick tip on combining (aggregating) result strings in SQL Server. Sometimes it's useful to add multivalued columns to results (or CSV files) but it's not all that simple.

For combining results of a JOIN to a single column you can use the STUFF function. Credit for this one goes to Devart.

SELECT
      m.maskid
    , m.maskname
    , m.schoolid
    , s.schoolname
    , maskdetail = STUFF((
          SELECT ',' + md.maskdetail
          FROM dbo.maskdetails md
          WHERE m.maskid = md.maskid
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM dbo.tblmask m
JOIN dbo.school s ON s.ID = m.schoolid
ORDER BY m.maskname

This will produce a nice column with a value like "value1, value2, value9".

Here's more info on string aggregation. MySQL has a similar function called GROUP_CONCAT.