Concatenation of Row Values using T-Sql

[Just copy & paste]


declare
@EmployeeDepartment table(DeptID int,EmpName varchar(50))

insert into @EmployeeDepartment(DeptID,EmpName)values(10,'Manish')
insert into @EmployeeDepartment(DeptID,EmpName)values(10,'Milan')
insert into @EmployeeDepartment(DeptID,EmpName)values(10,'Mukesk')
insert into @EmployeeDepartment(DeptID,EmpName)values(12,'Amit')
insert into @EmployeeDepartment(DeptID,EmpName)values(12,'Ashok')
insert into @EmployeeDepartment(DeptID,EmpName)values(12,'Anand')
insert into @EmployeeDepartment(DeptID,EmpName)values(13,'Suresh')
insert into @EmployeeDepartment(DeptID,EmpName)values(13,'Sagar')
insert into @EmployeeDepartment(DeptID,EmpName)values(14,'Pradeep')

select * from @EmployeeDepartment

SELECT E1.DeptID,
(
SELECT EmpName + ',' FROM @EmployeeDepartment E2
WHERE E2.DeptID= E1.DeptID
ORDER BY EmpName
FOR XML PATH('')
) AS Emp_Name
FROM @EmployeeDepartment E1
GROUP BY DeptID

Regards
Manish