Feb 9, 2010

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
DepartmentIDDepartmentName
1Sales
2IT
3Finance


Employee Table
EmployeeID DepartmentID EmployeeName
11Sales Person 1
21Sales Person 2
32Developer 1
42Developer 2
52DBA 1


Now you want the results like this:
DepartmentName EmployeeList
SalesSales Person 1, Sales Person 2
ITDeveloper 1, Developer 2, DBA
FinanceNULL





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.

1 comment:

Jayan Udayakantha said...

Hi Preethi!

This is a very beautiful solution and really useful. But I found the 'VALUE' function doesnt work in sql server and the xqueries are case sensitive. 'value' worked fine. Is there any option to make it case insensitive?
Thanks and regards.

Post a Comment