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
)as EmployeeList

FROM #Department D) input

If you know of any other ways, please reply back


Susantha said...

Here is another way of doing the same.

select O.DepartmentID,D.DepartmentName,
(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