Concatinating rows in a TABLE based on grouping in SQL Server

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROC [dbo].[ConatinateColumn]

AS

BEGIN

SELECT E1.DeptID,

(

SELECT CONVERT(VARCHAR(10),E2.EmpID) + '-' , E2.EmpName + ', '

FROM tblEmployee E2

WHERE E1.DeptID = E2.DeptID

FOR XML PATH('')

) AS Employees

FROM tblEmployee E1

GROUP BY DeptID

END