How to Find the Email/ Employee Name group wise. Comma separated List from Sql 2008
--How to Find the Email/ Employee Name group wise
IF(OBJECT_ID('tempdb.dbo.#TestTempEmail') > 0)
BEGIN
DROP TABLE #TestTempEMAIL
END
IF(OBJECT_ID('tempdb.dbo.#employeedetails') > 0)
BEGIN
DROP TABLE #employeedetail
END
Create table #TestTempEMAIL (EmployeeCode varchar(50),Type1 varchar(10))
insert into #TestTempEMAIL values
('Test0007','HR'),
('Test0027','HR'),
('Test0459','HR'),
('Test0011','Finance'),
('Test0078','Finance'),
('Test0237','Finance'),
('Test0289','Finance'),
('Test0029','IT'),
('Test0055','IT'),
('Test0239','IT'),
('Test0313','IT'),
('Test0052','Admin'),
('Test0010','Business'),
('Test0013','Business')
SELECT   EmployeeCode = STUFF((SELECT  ' '+EmployeeCode+ ' ,'
    FROM #TestTempEMAIL AS Test1 WHERE Type1 = Test.Type1
        ORDER BY Type1
     FOR XML PATH('')), 1, 1, ''),Type1,GETDATE()
from #TestTempEMAIL Test
GROUP BY Type1
ORDER BY Type1;

Output