CODEWITHSUNDEEP
sql-server
Mohamad Amir
Mohamad Amir

Reputation: 293

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

Upvotes: 28

Views: 214815

Answers (11)

RAHUL
RAHUL

Reputation: 1

(SELECT STUFF(( SELECT ',' + CONVERT(VARCHAR(100), column Name) FROM TBALE NAME FOR XML PATH('')), 1, 1, '')) as COLUMN NAME

Upvotes: 0

Yogi
Yogi

Reputation: 263

DECLARE @UserMaster TABLE(
    UserID INT NOT NULL,    
    UserName varchar(30) NOT NULL,
    Department varchar(30) NOT NULL
);



INSERT INTO @UserMaster VALUES (1,'User1', 'Admin')
INSERT INTO @UserMaster VALUES (2,'User1', 'Accounts')
INSERT INTO @UserMaster VALUES (3,'User2', 'Finance')
INSERT INTO @UserMaster VALUES (1,'User3', 'Sales')
INSERT INTO @UserMaster VALUES (2,'User3', 'Finance')

SELECT UserName, String_Agg(Department, ',') as Department 
FROM @UserMaster
group by UserName

Upvotes: 4

Taryn
Taryn

Reputation: 247620

You tagged the question with both sql-server and plsql so I will provide answers for both SQL Server and Oracle.

In SQL Server you can use FOR XML PATH to concatenate multiple rows together:

select distinct t.[user],
  STUFF((SELECT distinct ', ' + t1.department
         from yourtable t1
         where t.[user] = t1.[user]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') department
from yourtable t;

See SQL Fiddle with Demo.

In Oracle 11g+ you can use LISTAGG:

select "User",
  listagg(department, ',') within group (order by "User") as departments
from yourtable
group by "User"

See SQL Fiddle with Demo

Prior to Oracle 11g, you could use the wm_concat function:

select "User",
  wm_concat(department) departments
from yourtable
group by "User"

Upvotes: 43

Muhammad Awais
Muhammad Awais

Reputation: 4492

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:

enter image description here

Upvotes: 3

aamir khan
aamir khan

Reputation: 41

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

Upvotes: 1

Sagar Rawal
Sagar Rawal

Reputation: 1442

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

Upvotes: 13

Nandha kumar
Nandha kumar

Reputation: 763

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

Upvotes: 10

Yogendra
Yogendra

Reputation: 11

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

Upvotes: 1

saurabhmca2003
saurabhmca2003

Reputation: 19

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

Upvotes: 1

Prakashm88
Prakashm88

Reputation: 178

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.

Upvotes: 0

user2822374
user2822374

Reputation:

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

Upvotes: 11

Related Questions