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]