如何在一个逗号分隔值中获取列值 [英] 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

推荐答案

你用 sql-server 和 plsql 标记了问题，所以我将为 SQL Server 和 Oracle 提供答案.

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

在 SQL Server 中，您可以使用 FOR XML PATH 将多行连接在一起:

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;

参见SQL Fiddle with Demo.

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

In Oracle 11g+ you can use LISTAGG:

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

参见SQL Fiddle with Demo

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

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

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

这篇关于如何在一个逗号分隔值中获取列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！