Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 5 down vote accepted

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
    SELECT role + ','
    FROM employee AS intern
    WHERE =
    FOR XML PATH('')
) pre_trimmed (roles)
GROUP BY name, roles;

These details are gathered from different sources:

share|improve this answer

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

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

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

share|improve this answer
Will there ever be 3 roles? – gbn Jul 20 '11 at 8:30
Also, added to gbn's question, this doesn't give a correct answer, it will return: [bob, janitor, developer] and also [bob, developer, janitor]. – Marian Jul 20 '11 at 8:41

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.