Finding employees specific to department in SQL Server 2000

Question

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

Answer 1

You can't in SQL Server 2000 without using a scalar user defined function in the SELECT. This udf will have a local variable (as you have mentioned) and concats each grouping

select
    Dept, dbo.MyConcatUDF (Dept)
FROM
    (SELECT DISTINCT Dept FROM tblEmp) t1

Or do it in the client