CREATE TABLE EMPLOYEE([EID] INT, [ENAME] VARCHAR(45),[DEPT] VARCHAR(45));
INSERT INTO EMPLOYEE VALUES (1, 'Mahesh','IT');
INSERT INTO EMPLOYEE VALUES (2, 'Suresh','ACCOUNTS');
INSERT INTO EMPLOYEE VALUES (3, 'Prakesh','ACCOUNTS');
INSERT INTO EMPLOYEE VALUES (4, 'Rajesh','IT');
INSERT INTO EMPLOYEE VALUES (5, 'Kamesh','ACCOUNTS');
INSERT INTO EMPLOYEE VALUES (6, 'Prajnesh','IT');
INSERT INTO EMPLOYEE VALUES (7, 'Sateesh','IT');
INSERT INTO EMPLOYEE VALUES (8, 'Venkatesh','SALES');
INSERT INTO EMPLOYEE VALUES (9, 'Rupesh','ACCOUNTS');
INSERT INTO EMPLOYEE VALUES (10, 'Yagnesh','SALES');
SELECT T1.[DEPT], STUFF((SELECT ' '+[ENAME]
FROM [EMPLOYEE] T2
WHERE T2.[DEPT]=T1.[DEPT]
FOR XML PATH('')),1,1,'') AS ENAME_LIST
FROM [EMPLOYEE] T1
GROUP BY [DEPT]
