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/MariaDB has a similar function called GROUP_CONCAT.