5

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.

7

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:

-1

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

  • 3
    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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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