SQL 如何在 Oracle 中将多行组合成逗号分隔的列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/468990/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I combine multiple rows into a comma-delimited list in Oracle?
提问by rics
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;
采纳答案by Daniel Emge
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.
回答by JoshL
The WM_CONCATfunction (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函数（如果包含在您的数据库中，在 Oracle 11.2 之前）或LISTAGG（从 Oracle 11.2 开始）应该可以很好地完成任务。例如，这将获取架构中以逗号分隔的表名列表：
select listagg(table_name, ', ') within group (order by table_name)
from user_tables;
or
或者
select wm_concat(table_name)
from user_tables;
回答by Decci.7
You can use this as well:
您也可以使用它：
SELECT RTRIM (
XMLAGG (XMLELEMENT (e, country_name || ',')).EXTRACT ('//text()'),
',')
country_name
FROM countries;
回答by Gaya3
you can try this query.
你可以试试这个查询。
select listagg(country_name,',') within group (order by country_name) cnt
from countries;
回答by tuinstoel
The fastest way it is to use the Oracle collect function.
最快的方法是使用 Oracle 收集功能。
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 功能可以实现您的需求。
回答by tips
I needed a similar thing and found the following solution.
我需要类似的东西并找到了以下解决方案。
select RTRIM(XMLAGG(XMLELEMENT(e,country_name || ',')).EXTRACT('//text()'),',') country_name from
回答by David MacIntosh
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 发票保留原因列表带入一个字段以进行标题级别查询：
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;
回答by Andrew Wood
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
回答by user1626874
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
