[sql-server] How to get column values in one comma separated value

I have a table which 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 which results following format

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

For Oracle versions which does not support the WM_CONCAT, the following can be used

  select "User", RTRIM(
     XMLAGG (XMLELEMENT(e, department||',') ORDER BY department).EXTRACT('//text()') , ','
     ) AS departments 
  from yourtable 
  group by "User"

This one is much more powerful and flexible - you can specify both delimiters and sort order within each group as in listagg.

Try the following Query:

select distinct Users,
 STUFF(
        (
        select ', ' + d.Department FROM @temp d
        where t.Users=d.Users
group by d.Department for xml path('')
        ), 1, 2, '') as Departments
 from @temp t

Implementation:

Declare @temp Table(
ID int,
Users varchar(50),
Department varchar(50)
)
insert into @temp
(ID,Users,Department)
values
(1,'User1','Admin')
insert into @temp
(ID,Users,Department)
values
(2,'User1','Accounts')
insert into @temp
(ID,Users,Department)
values
(3,'User2','Finance')
insert into @temp
(ID,Users,Department)
values
(4,'User3','Sales')
insert into @temp
(ID,Users,Department)
values
(5,'User3','Finance')

 select distinct Users,
 STUFF(
        (
        select ', ' + d.Department FROM @temp d
        where t.Users=d.Users
group by d.Department for xml path('')
        ), 1, 2, '') as Departments

 from @temp t

Result will be:

You can do this with the following SQL:

SELECT STUFF
(
    (
        SELECT ',' + s.FirstName 
        FROM Employee s
        ORDER BY s.FirstName FOR XML PATH('')
    ),
     1, 1, ''
) AS Employees

SELECT name, GROUP_CONCAT( section ) 
FROM  `tmp` 
GROUP BY name

In Sql Server you can use it.

DECLARE @UserMaster TABLE( 

    UserID INT NOT NULL, 

    UserName varchar(30) NOT NULL 

); 

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

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

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

SELECT * FROM @UserMaster

DECLARE @CSV VARCHAR(MAX) 

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

SELECT @CSV AS Result

I think it will be easy to you. I am using group_concat which concatenate diffent values with separator as we have defined

select ID,User, GROUP_CONCAT(Distinct Department order  by Department asc 

separator ', ') as Department from Table_Name   group by ID

MYSQL: To get column values as one comma separated value use GROUP_CONCAT( ) function as

GROUP_CONCAT(  `column_name` )

for example

SELECT GROUP_CONCAT(  `column_name` ) 
FROM  `table_name` 
WHERE 1 
LIMIT 0 , 30

For Mysql:

SELECT t.user, 

(SELECT GROUP_CONCAT( t1.department ) FROM table_name t1 WHERE t1.user = t.user)department

FROM table_name t
GROUP BY t.user
LIMIT 0 , 30