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



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

2 comments:

Susantha said...

Here is another way of doing the same.

select O.DepartmentID,D.DepartmentName,
stuff(
(select ','+I.EmployeeName from #Employee I
where O.DepartmentID=I.DepartmentID
for xml path('')),1,1,''
)
from #Employee O
inner join #Department D
on O.DepartmentID=D.DepartmentID
group by O.DepartmentID,D.DepartmentName

Preethiviraj Kulasingham said...

Great Code, Susantha

Post a Comment