SQL query that concatenates values from duplicate rows in a single table

Let's say I have a table of employees, like this:

Name     Sex     Role

Bob      M       Developer
Joe      M       QA

Now, I have a problem with duplicated rows in this table. I will fix it sometime but it's not the issue. What I currently need is something that queries such a table:

Name     Sex     Role

Bob      M       Developer
Bob      M       Janitor
Joe      M       QA
Joe      M       CEO

And will output the following rows:

Name     Role

Bob      Developer, Janitor
Joe      QA, CEO

It doesn't matter to me if the "Role" will be split to separate columns or be a single column with multiple values.

Using SQL Server 2008 if it matters.

Group_concat as in MySQL is not available in SQL Server up to SQL Server 2016. vNext will introduce STRING_AGG however that provides equivalent functionality.

You have some manual options though:

• build a scalar function that takes an employee name/id as parameter and shows concatenated role values and apply this function to each employee

• use xml functions

• use an already built aggregate (similar as first option)

A solution for your current case (just took Brad's answer from SO and customized for your table) would be:

SELECT name, LEFT(roles , LEN(roles )-1) AS roles
FROM employee AS extern
CROSS APPLY
(
    SELECT role + ','
    FROM employee AS intern
    WHERE extern.name = intern.name
    FOR XML PATH('')
) pre_trimmed (roles)
GROUP BY name, roles;

These details are gathered from different sources:

• Simulating group_concat MySQL function in MS SQL Server 2005? - on StackOverflow.com

• Emulating MySQL’s GROUP_CONCAT() Function in SQL Server 2005 - blog

• GROUP_CONCAT in SQL Server - blog

• T-SQL Equivalent For Group_Concat() Function - MSDN

StackExchange Database Administrators Q#3965, answer score: 7