Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 4 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
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:

share|improve this answer
    
ddeimeke's answer was good enough for me but I assume yours is more accurate. –  yellowblood Jul 24 '11 at 14:23

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".

share|improve this answer
    
This worked perfectly, thanks! –  yellowblood Jul 20 '11 at 8:21
2  
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

 
discard

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.