SQL Server : Group multiple row values into comma-separated values

You may need to aggregate a column values for a given group of rows in a comma-separated string.

Lets say you have the following table:

DECLARE @tbl TABLE (JobId INT, TechSkills VARCHAR(100))

INSERT @tbl 
VALUES
(1, '.Net Framework'),
(1, 'C#'),
(1, 'PHP'),
(2, '.Net Core'),
(2, 'Laravel'),
(3, 'Angular'),
(3, 'React')

In this table, we have 3 distinct JobIds with multiple technical skills. If we use simple select statement it will return multiple rows for each JobId.

select * from @tbl

You can use subquery with FOR XML PATH to aggregate multiple row values in a single cell value.

SELECT distinct
    JobId,
    (
        SELECT ',' + TechSkills
        FROM @tbl t2
        WHERE t2.JobId = t1.JobId
        FOR XML PATH('')
    ) Concatenated
FROM @tbl t1

In the output you may notice that each value contains an extra comma at the beginning of the text value.

You can use STUFF function to remove this extra comma at the beginning of text string.

SELECT distinct
    JobId, STUFF(
    (
        SELECT ','+ TechSkills
        FROM @tbl t2
        WHERE t2.JobId = t1.JobId
        FOR XML PATH('')
    ),1,2,'') Concatenated
FROM @tbl t1