如何在一个逗号分隔的值中获取列值

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

9 个解决方案

#1

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

您将问题标记为SQL - Server和plsql，因此我将为SQL Server和Oracle提供答案。

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

在SQL Server中，可以使用XML路径将多个行连接在一起:

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.

查看SQL小提琴与演示。

In Oracle 11g+ you can use LISTAGG:

在Oracle 11g+中，您可以使用LISTAGG:

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

See SQL Fiddle with Demo

参见SQL小提琴演示

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

在Oracle 11g之前，您可以使用wm_concat函数:

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

#2

In Sql Server you can use it.

在Sql Server中，您可以使用它。

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

#3

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

MYSQL:要将列值作为一个逗号分隔的值，请使用GROUP_CONCAT()函数作为

GROUP_CONCAT(  `column_name` )

for example

例如

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

#4

You can do this with the following SQL:

您可以使用以下SQL执行此操作:

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

#5

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

#6

For Mysql:

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

#7

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:

#8

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

对于不支持WM_CONCAT的Oracle版本，可以使用以下命令

  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.

这个功能更加强大和灵活——您可以在每个组中指定分隔符和排序顺序，就像在listagg中一样。

#9

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

我想这对你来说很容易。我正在使用group_concat，它将扩散值与我们定义的分隔符连接在一起

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

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