This way
CREATE table #Tbl_Dept
(
Dept_ID INT IDENTITY(1,1),
Dept_Name VARCHAR(30)
);
INSERT INTO #Tbl_Dept VALUES('.NET')
GO
INSERT INTO #Tbl_Dept VALUES('Java')
Go
INSERT INTO #Tbl_Dept VALUES('Php')
GO
CREATE table #Tbl_Emp
(
Emp_ID INT IDENTITY(1,1),
Dept_ID INT,
Emp_Name VARCHAR(30)
);
INSERT INTO #Tbl_Emp VALUES(1,'Ram')
GO
INSERT INTO #Tbl_Emp VALUES(2,'Mohan')
Go
INSERT INTO #Tbl_Emp VALUES(3,'Sohan')
GO
INSERT INTO #Tbl_Emp VALUES(2,'Reeta')
Go
INSERT INTO #Tbl_Emp VALUES(3,'Sonu')
GO
INSERT INTO #Tbl_Emp VALUES(1,'Karan')
GO
SELECT Dept_ID, Dept_Name
, (SELECT CAST(Emp_Name + ', ' AS VARCHAR(MAX))
FROM #Tbl_Emp
WHERE (Dept_ID = Departments.Dept_ID)
FOR XML PATH ('')
) AS Locations
from #Tbl_Dept Departments
drop table #Tbl_Emp
drop table #Tbl_Dept
Output: