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: 143
sql-server-2000
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
