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:
|1||1||Sales Person 1|
|2||1||Sales Person 2|
Now you want the results like this:
|Sales||Sales Person 1, Sales Person 2|
|IT||Developer 1, Developer 2, DBA|
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:
FROM dbo.Employee E
WHERE D.DepartmentID = E.DepartmentID
FOR XML PATH (''), TYPE, ROOT).VALUE('root', 'varchar(max)'),1,1,'')) AS EmployeeeName
FROM dbo.Department D
Hope this helps.