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')



Now comes the code which gives the results:

SELECT DepartmentName, LEFT(EmployeeList, LEN(EmployeeList)-1) as EmployeeList FROM
(
SELECT D.DepartmentName,
(SELECT E.EmployeeName +','
FROM #Employee E
WHERE D.DepartmentID = E.DepartmentID
FOR XML PATH('')
)as EmployeeList

FROM #Department D) input


If you know of any other ways, please reply back