How to get column values in one comma separated value in SQL?

Question

I have a table that contains rows like below

ID  User      Department
1   User1     Admin
2   User1     Accounts
3   User2     Finance
4   User3     Sales
5   User3     Finance

I need a select query that results in following format

ID  User      Department
1   User1     Admin,Accounts
2   User2     Finance
3   User3     Sales, Finance

Answer 1

To get column values in one comma-separated value in SQL.

DECLARE @UserMaster TABLE( 

    UserID INT NOT NULL, 

    UserName varchar(30) NOT NULL 

); 

INSERT INTO @UserMaster VALUES (1,'Krishna')

INSERT INTO @UserMaster VALUES (2,'Arpit')

INSERT INTO @UserMaster VALUES (3,'Sandeep')

SELECT * FROM @UserMaster

DECLARE @CSV VARCHAR(MAX) 

SELECT @CSV = COALESCE(@CSV + ', ', '') + UserName from @UserMaster 

SELECT @CSV AS Result

Check out our SQL Online Course to have a complete grip on SQL concepts.