Display Comma Separated Values of a column group by another column in a different table

Hey guys!
Let’s get into another SQL code.
You have 2 tables TBL_EMP and TBL_DEPT with following schema and data:
TBL_EMP:
Emp_ID Dept_ID Emp_Name
1 1 Ram
2 2 Mohan
3 3 Sohan
4 2 Rita
5 3 Sonu
6 1 Karan

TBL_DEPT:
Dept_ID Dept_Name
1 .Net
2 Java
3 Php

Now you need to write SQL code that gives following output:
Dept_Name Emp_Name
.Net Ram,Karan
Java Mohan,Rita

Here goes the SQL program:

DECLARE @tmpTbl TABLE
(
ID BIGINT IDENTITY(1,1),
–Dept_ID int,
Dept_Name NVARCHAR(100),
Emp_Name nvarchar(100)
)

DECLARE @tmpResults TABLE
(
ID BIGINT IDENTITY(1,1),
Dept_Name NVARCHAR(100),
Emp_Name nvarchar(max)
)

DECLARE @emp_Name TABLE
(
Emp_Name nvarchar(max)
)

DECLARE @rowCnt int, @sql nvarchar(MAX), @counter int = 1,
@deptName nvarchar(100), @empName nvarchar(max)

INSERT INTO @tmpTbl (Dept_Name,Emp_Name)
SELECT TD.Dept_Name, TE.Emp_Name FROM Tbl_Dept TD
JOIN Tbl_Emp TE
ON TD.Dept_ID = TE.Dept_ID

SELECT @rowCnt = COUNT(*) FROM @tmpTbl

WHILE(@counter<=@rowCnt)
BEGIN

SELECT @deptName = Dept_Name FROM @tmpTbl WHERE ID = @counter

SELECT @SQL = COALESCE(@SQL,”,”) + Emp_Name + ‘,’ FROM @tmpTbl
WHERE Dept_Name = @deptName

SET @sql = LEFT(@SQL, LEN(@SQL)-1)

INSERT INTO @tmpResults (Dept_Name, Emp_Name)
SELECT @deptName, @sql

SET @sql=”

SET @counter+=1

END

SELECT DISTINCT Dept_Name, Emp_Name FROM @tmpResults

Enjoy!
Happy Coding!