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

我有一个简单的问题:

select * from countries

结果如下:

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

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

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?

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

我的问题是基于 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;
465238 次浏览
小开

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

你也可以这样做:

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

访问这个网站，询问 tom，搜索“ stragg”或者“ string 拼接” 还有一个没有文档说明的 Oracle 函数来满足您的需求。

小开

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

That is,

select country_name||', ' country from countries

两边都有点绕口令。

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

问问汤姆

小开
最佳答案

这里有一个不需要 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.

正如其他人提到的，如果你在11gR2或更大，你现在可以使用 listagg，这是简单得多。

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


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


1 row selected.
小开

你也可以这样做:

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

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

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

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;

更多详情/选项

链接到文档

小开

对于 Oracle，可以使用 李斯塔格

小开

you can use this query to do the above task

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

小开 
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
小开

在这个例子中，我们创建了一个函数，将一个逗号描述的不同行级别的 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;
小开

您可以尝试这个查询。

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

提交答案