Showing posts with label XML. Show all posts
Showing posts with label XML. Show all posts

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



Oct 26, 2009

Here is the code for yesterday's Scenario:
create table #Department
(
DepartmentID int not null primary key clustered,
DepartmentName varchar(10) not null
)

create table #Employee
(
EmployeeID int not null identity primary key clustered,
DepartmentID int,
EmployeeName varchar(100)
)


insert #Department values(1, 'Sales'), (2, 'IT'), (3, 'Finance')
insert #Employee values (1, 'Sales Person1'), (1, 'Sales Person 2'), (2, 'Developer 1'), (2, 'Developer 2'),(2, 'DBA')