question about regrouping the rows

soujanya nomula
soujanya nomula used Ask the Experts™
on
hi all,

I have a similar issue posted here, but not exactly the same.

I have a table
emp_dept
(emp_id numeric,
dept_id numeric,
department char(3) )

employees
(emp_id numeric,
 emp_name varchar(100)
)

1: 1 employee can belong to 2 different departments
2: I want the output like
emp id, emp_name, (list of all departments the emp_id belongs to )--separated by comma)

this is for displaying, I know we can use a temp table etc to do this, but was looking for any better approach.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented: 
SELECT
  e.emp_id
, e.emp_name
, ca1.departments
FROM [employees] AS e
    CROSS APPLY (
        SELECT
        STUFF((
              SELECT
                    ', ' + d.department
              FROM [emp_dept] AS d
              WHERE d.emp_id = e.emp_id
              ORDER BY  d.department --<< if wanted
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca1 (departments)
;

Open in new window

{+edit} added: order by

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial