String "Aggregates" Solution

Even though I promised to update a solution to a problem,  I failed to do so for a long time.

Here is the problem

If you need to get the sum of salary by department we may be able to use Sum and group by clauses in a single select statement to get it done.  But if you have a situation where you need to give a comma separated string of employees for each department, how will you handle it?
Consider these tables:

Department Table

DepartmentID	DepartmentName
1	Sales
2	IT
3	Finance

Employee Table

EmployeeID	DepartmentID	EmployeeName
1	1	Sales Person 1
2	1	Sales Person 2
3	2	Developer 1
4	2	Developer 2
5	2	DBA 1

Now you want the results like this:

DepartmentName	EmployeeList
Sales	Sales Person 1, Sales Person 2
IT	Developer 1, Developer 2, DBA
Finance	NULL

There are couple of ways of doing this: 

• Loop through for each department and construct the string and store in a  temporary table; Finally retrieve from that temporary table

• Use the same mechanism but use all departments at a time.  Need to loop through the rest of the employees
• Create a CLR function to do this.

Is there any other method?
There could be many solutions to this problem, this is one of them.  It uses FOR XML clause

Try this option:

SELECT D.DepartmentName,
(STUFF((SELECT  ','+E.EmployeeName  
        FROM dbo.Employee E 
        WHERE D.DepartmentID = E.DepartmentID 
        FOR XML PATH (''), TYPE, ROOT).VALUE('root[1]', 'varchar(max)'),1,1,'')) AS EmployeeeName
FROM dbo.Department D

Hope this helps.