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