如何将多个行合并到Oracle中以逗号分隔的列表中?(复制)

This question already has an answer here:

这个问题已经有了答案:

SQL Query to concatenate column values from multiple rows in Oracle 13 answers
SQL查询，从Oracle 13答案中的多个行连接列值

I have a simple query:

我有一个简单的问题:

select * from countries

with the following results:

用下面的结果:

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

I would like to return the results in one row, so like this:

我想以一行的形式返回结果，如下所示:

Albania, Andorra, Antigua, ...

Of course, I can write a PL/SQL function to do the job (I already did in Oracle 10g), but is there a nicer, preferably non-Oracle-specific solution (or may be a built-in function) for this task?

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

I would generally use it to avoid multiple rows in a sub-query, so if a person has more then one citizenship, I do not want her/him to be a duplicate in the list.

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

My question is based on the similar question on SQL server 2005.

我的问题基于SQL server 2005上的类似问题。

UPDATE: My function looks like this:

更新:我的函数是这样的:

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;

11 个解决方案

#1

Here is a simple way without stragg or creating a function.

这里有一种简单的方法，不用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.

As others have mentioned, if you are on 11g R2 or greater, you can now use listagg which is much simpler.

正如其他人提到的，如果您在11g R2或更高的级别上，您现在可以使用listagg，它要简单得多。

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

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

1 row selected.

#2

106

The WM_CONCAT function (if included in your database, pre Oracle 11.2) or LISTAGG (starting Oracle 11.2) should do the trick nicely. For example, this gets a comma-delimited list of the table names in your schema:

WM_CONCAT函数(如果包含在数据库中，则是pre 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;

More details/options

更多细节/选项

Link to documentation

链接到文档

#3

For Oracle you can use LISTAGG

对于Oracle，您可以使用LISTAGG

#4

you can try this query.

您可以尝试这个查询。

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

#5

You can use this as well:

你也可以使用这个:

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

#6

The fastest way it is to use the Oracle collect function.

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

You can also do this:

你也可以这样做:

select *
  2    from (
  3  select deptno,
  4         case when row_number() over (partition by deptno order by ename)=1
  5             then stragg(ename) over
  6                  (partition by deptno
  7                       order by ename
  8                         rows between unbounded preceding
  9                                  and unbounded following)
 10         end enames
 11    from emp
 12         )
 13   where enames is not null

Visit the site ask tom and search on 'stragg' or 'string concatenation' . Lots of examples. There is also a not-documented oracle function to achieve your needs.

访问网站询问tom并搜索“stragg”或“string concatenation”。大量的例子。还有一个未文档化的oracle函数来满足您的需求。

#7

I needed a similar thing and found the following solution.

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

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

#8

In this example we are creating a function to bring a comma delineated list of distinct line level AP invoice hold reasons into one field for header level query:

在这个例子中，我们正在创建一个函数，将一个由逗号分隔的不同行的AP invoice保持原因列表带入一个字段，用于头级查询:

 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;

#9

I have always had to write some PL/SQL for this or I just concatenate a ',' to the field and copy into an editor and remove the CR from the list giving me the single line.

我总是需要为这个写一些PL/SQL，或者我只是将一个'，'的字段和副本连接到一个编辑器中，然后从列表中移除CR，给我一行。

That is,

也就是说,

select country_name||', ' country from countries

A little bit long winded both ways.

两方面都有点冗长。

If you look at Ask Tom you will see loads of possible solutions but they all revert to type declarations and/or PL/SQL

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

Ask Tom

问汤姆

#10

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

#11

-2

you can use this query to do the above task

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

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

for detail and step by step explanation visit the following link
http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html

详细信息和逐步解释请访问以下链接http://oops-solution.blogspot.com/2011/11/sql-server-convert-table- colum- data.html

#1