Finding employees specific to department in SQL SERVER 2000(SET BASED)

Posted by xyz on Stack Overflow See other posts from Stack Overflow or by xyz
Published on 2010-03-24T04:21:14Z Indexed on 2010/03/24 4:23 UTC
Read the original article Hit count: 136

Filed under:

Suppose I have a table (tblEmp) whose structure is like as under

Dept     Emp

d1  e1

d1  e2

d1  e3

d2  e4

d2  e5

d3  e6

If I need to bring the output as

Dept    DepartmentSpecificEmployees

d1  e1,e2,e3

d2  e4,e5

d3  e6

I will write the query as

select Dept, 

stuff((select Emp + ',' from tblEmp t2 where t1.Dept = t2.Dept for xml path(''),1,1,'')DepartmentSpecificEmployees

from tblEmp t1

group by  Dept

But this will work in Sql Server 2005+.

How can I achieve the same in Sql Server 2000 without any variable declaration or loop or cursor?

If I use COALESCE as an alternative, then I need to use a variable which will defeat the purpose

Please help

© Stack Overflow or respective owner

Related posts about sql-server-2000