在 Oracle 中如何将多行组合成逗号分隔的列表？

Question

我有一个简单的查询：

select * from countries

结果如下：

country_name
------------
Albania
Andorra
Antigua
.....

我想在一行中返回结果，如下所示：

Albania, Andorra, Antigua, ...

当然，我可以编写一个 PL/SQL 函数来完成这项工作（我已经在 Oracle 10g 中做到了），但是是否有更好的、最好是非 Oracle 特定的解决方案（或者可能是内置函数）来完成此任务？

我通常会使用它来避免子查询中出现多行，因此如果一个人拥有多个公民身份，我不希望她/他在列表中重复。

我的问题基于 SQL Server 2005。

更新： 我的函数如下所示：

CREATE OR REPLACE FUNCTION APPEND_FIELD (sqlstr in varchar2, sep in varchar2 ) return varchar2 is
ret varchar2(4000) := '';
TYPE cur_typ IS REF CURSOR;
rec cur_typ;
field varchar2(4000);
begin
     OPEN rec FOR sqlstr;
     LOOP
         FETCH rec INTO field;
         EXIT WHEN rec%NOTFOUND;
         ret := ret || field || sep;
     END LOOP;
     if length(ret) = 0 then
          RETURN '';
     else
          RETURN substr(ret,1,length(ret)-length(sep));
     end if;
end;

Answer 1

我需要类似的东西并找到了以下解决方案。

select RTRIM(XMLAGG(XMLELEMENT(e,country_name || ',')).EXTRACT('//text()'),',') country_name from  

Answer 2

在此示例中，我们将创建一个函数，将逗号分隔的不同行级别 AP 发票保留原因列表放入一个字段中以进行标题级别查询：

 FUNCTION getHoldReasonsByInvoiceId (p_InvoiceId IN NUMBER) RETURN VARCHAR2

  IS

  v_HoldReasons   VARCHAR2 (1000);

  v_Count         NUMBER := 0;

  CURSOR v_HoldsCusror (p2_InvoiceId IN NUMBER)
   IS
     SELECT DISTINCT hold_reason
       FROM ap.AP_HOLDS_ALL APH
      WHERE status_flag NOT IN ('R') AND invoice_id = p2_InvoiceId;
BEGIN

  v_HoldReasons := ' ';

  FOR rHR IN v_HoldsCusror (p_InvoiceId)
  LOOP
     v_Count := v_COunt + 1;

     IF (v_Count = 1)
     THEN
        v_HoldReasons := rHR.hold_reason;
     ELSE
        v_HoldReasons := v_HoldReasons || ', ' || rHR.hold_reason;
     END IF;
  END LOOP;

  RETURN v_HoldReasons;
END; 

Answer 3

我总是必须为此编写一些 PL/SQL，或者我只是将一个“,”连接到该字段并复制到编辑器中，然后从列表中删除 CR，从而得到单行。

也就是说，

select country_name||', ' country from countries

双向有点啰嗦。

如果您查看 Ask Tom，您会看到大量可能的解决方案，但它们都恢复为类型声明和/或 PL/SQL

问汤姆

Answer 4

SELECT REPLACE(REPLACE
((SELECT     TOP (100) PERCENT country_name + ', ' AS CountryName
FROM         country_name
ORDER BY country_name FOR XML PATH('')), 
'&<CountryName>', ''), '&<CountryName>', '') AS CountryNames

Answer 5

您可以使用此查询来执行上述任务，

DECLARE @test NVARCHAR(max)
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test SELECT field2= @test

有关详细信息和分步说明，请访问以下链接
http://oops-solution.blogspot .com/2011/11/sql-server-convert-table-column-data.html

Answer 6

WM_CONCAT 函数（如果包含在 Oracle 11.2 之前的数据库中）或 LISTAGG（从 Oracle 11.2 开始）应该可以很好地实现这一目的。 例如，这会获取架构中以逗号分隔的表名称列表：

select listagg(table_name, ', ') within group (order by table_name) 
  from user_tables;

或

select wm_concat(table_name) 
  from user_tables;

更多详细信息/选项

文档链接

Answer 7

这是一种无需 stragg 或创建函数的简单方法。

create table countries ( country_name varchar2 (100));

insert into countries values ('Albania');

insert into countries values ('Andorra');

insert into countries values ('Antigua');


SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
      FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
                   COUNT (*) OVER () cnt
              FROM countries)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

CSV                                                                             
--------------------------
Albania,Andorra,Antigua                                                         

1 row selected.

正如其他人提到的，如果您使用的是 11g R2 或更高版本，您现在可以使用更简单的 listagg。

select listagg(country_name,', ') within group(order by country_name) csv
  from countries;

CSV                                                                             
--------------------------
Albania, Andorra, Antigua

1 row selected.

Answer 8

对于 Oracle，您可以使用 LISTAGG

Answer 9

你可以尝试这个查询。

select listagg(country_name,',') within group (order by country_name) cnt 
from countries; 

Answer 10

你也可以使用这个：

SELECT RTRIM (
          XMLAGG (XMLELEMENT (e, country_name || ',')).EXTRACT ('//text()'),
          ',')
          country_name
  FROM countries;

Answer 11

最快的方法是使用Oracle的collect函数。

您还可以执行以下操作：

  SELECT *
FROM   (SELECT deptno,
               CASE
                 WHEN Row_number()
                        over (
                          PARTITION BY deptno
                          ORDER BY ename) = 1 THEN Stragg(ename)
                 over (
                   PARTITION BY deptno
                   ORDER BY ename ROWS BETWEEN unbounded
                 preceding AND unbounded following)
               END enames
        FROM   emp)
WHERE  enames IS NOT NULL 

访问网站询问 Tom 并搜索 'stragg' 或 'string concatenation' 。 很多
例子。 还有一个未记录的 oracle 函数可以实现您的需求。