Combine multiple rows into a comma separated Column in SQL

If we required comma separated values form one to column in multiple rows.

Example

CREATE TABLE EMP
(
EID INT IDENTITY,
ENAME VARCHAR(100),
DEPT VARCHAR(100)
)

INSERT INTO EMP(ENAME,DEPT)
VALUES(‘ABC’,’SALE’),
(‘PQR’,’IT’),
(‘XYZ’,’HR’),
(‘MNL’,’ADMIN’)

DECLARE @DEPT VARCHAR(255)

SELECT @DEPT=COALESCE(@DEPT + ‘, ‘, ”) + CAST(DEPT AS VARCHAR(100))
FROM EMP

SELECT @DEPT as ‘Department’