如何将多行组合到Oracle中以逗号分隔的列表中?
我有一个简单的查询:如何将多行组合到Oracle中以逗号分隔的列表中?
select * from countries
结果如下:
country_name
------------
Albania
Andorra
Antigua
.....
我想返回的结果在一排,所以像这样的:
Albania, Andorra, Antigua, ...
中当然,我可以编写一个PL/SQL函数来完成这项工作(我已经在Oracle 10g中完成了这项工作),但是有没有更好的,最好是非Oracle特定解决方案(或者可能是内置函数)问?
我一般会用它来避免子查询中的多行,所以如果一个人拥有多于一个国籍,我不希望她/他在列表中重复。
我的问题是基于SQL server 2005上的类似问题。
UPDATE: 我的功能看起来是这样的:
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;
这里是没有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.
不错的短期解决方案,但一些错别字破坏它。 此行应为: FROM(SELECT COUNTRY_NAME,ROW_NUMBER()OVER(ORDER BY COUNTRY_NAME)RN,使用LISTAGG功能 – 2009-01-26 21:26:39
JoshL的建议是使用11.2或更新任何人都非常可取 – JakeRobb 2015-06-08 21:10:39
只要确保你的连接结果。不要超过oracle数据库的VARCHAR2最大长度限制(最可能的是4000个字节),否则会遇到`ORA-01489字符串连接的结果太长了。 – JanM 2017-06-21 09:37:26
最快的方法是使用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
亲临现场问汤姆和“stragg”或“字符串连接”进行搜索。很多 的例子。还有一个未记录的oracle函数来实现你的需求。
我总是不得不为这个编写一些PL/SQL,或者我只是将一个','连接到该字段并复制到一个编辑器中,并从列表中删除给出单行的CR。
也就是说,
select country_name||', ' country from countries
一点点长篇大论两种方式。
如果你看看问汤姆,你会看到可能的解决方案的负荷,但他们都回到类型声明和/或PL/SQL
您可以使用此还有:
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
功能(如果包括在你的数据库,甲骨文前期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;
你可以使用此查询上面做任务
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;
你能发布你的PL/SQL代码吗? – tuinstoel 2009-01-22 13:10:50