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

Question

Lets say I have a table of employees, like this:

Name     Sex     Role

Bob      M       Developer
Joe      M       QA

Now, I have a problem with this table of duplicated rows. 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.

Answer 1

Group_concat as in MySQL is not available in SQL Server up to the current version.

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

Answer 2

I would try it with the following, but please be aware, that this can run quite a time on very big tables.

select a.name, a.role, b.role from employees a, employees b where a.name=b.name and a.role<>b.role

I called the table "employees" and the columns "name" and "role".