在 ORACLE 中，有没有办法使用两个表将多行行连接成一个行，其中最终值用逗号分隔？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4370820/
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
Is there a way, in ORACLE, to join multiple row lines into a single one, using two tables, where the final values are separated by commas?
提问by nunofmendes
Is there a way to join multiple row lines a single one using two tables where the values are separated by commas in ORACLE?
有没有办法使用两个表将多行行连接成一个行，其中值在 ORACLE 中用逗号分隔？
Example:
例子：
Table1
表格1
IdN Name
---------
1 A
2 B
3 C
Table 2
表 2
IdC Car
------------
1 Ferrari
1 BMW
2 SEAT
2 FIAT
3 FORD
Result as:
结果为：
A Ferrari,BMW
B SEAT,FIAT
C FORD
I was wondering if there is something like this:
我想知道是否有这样的事情：
SELECT NAME,CAR
FROM TABLE1, TABLE2
where TABLE1.IdN=TABLE2.IdC
This returns something like:
这将返回如下内容：
A FERRARI
A BMW
B SEAT
B FIAT
C FORD
A 法拉利
A 宝马
B 座椅
B 菲亚特
C 福特
Is there a simple way to "concat" into a row with comma-separated-values ?
有没有一种简单的方法可以用逗号分隔值“连接”成一行？
回答by Lou Franco
Take a look at LISTAGG
看看 LISTAGG
http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php
http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php
Something like:
就像是：
SELECT NAME, LISTAGG(CAR, ',') WITHIN GROUP (ORDER BY CAR) AS CARS
FROM (SELECT NAME,CAR
FROM TABLE1, TABLE2
where TABLE1.IdN=TABLE2.IdC)
GROUP BY NAME;
回答by Randy
look up user defined aggregate functions. if you really need to list them all in one column, you can set up an aggregate function, and it will do that for you.
查找用户定义的聚合函数。如果你真的需要在一列中列出它们，你可以设置一个聚合函数，它会为你做这些。
Declare
sql_txt Varchar2(4000);
Rec_cnt Number;
Begin
Select Count(*)
Into Rec_Cnt
From User_Types
Where Type_Name = 'VCARRAY'
And Typecode = 'COLLECTION';
If Rec_Cnt = 0 Then
EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE vcArray as table of varchar2(32000)';
END IF;
END;
/
CREATE OR REPLACE TYPE comma_list_agr_type as object
(
data vcArray,
static function
ODCIAggregateInitialize(sctx IN OUT comma_list_agr_type )
return number,
member function
ODCIAggregateIterate(self IN OUT comma_list_agr_type ,
value IN varchar2 )
return number,
member function
ODCIAggregateTerminate(self IN comma_list_agr_type,
returnValue OUT varchar2,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT comma_list_agr_type,
ctx2 IN comma_list_agr_type)
return number
);
/
CREATE OR REPLACE TYPE BODY comma_list_agr_type
is
static function ODCIAggregateInitialize(sctx IN OUT comma_list_agr_type)
return number
is
begin
sctx := comma_list_agr_type( vcArray() );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT comma_list_agr_type,
value IN varchar2 )
return number
is
begin
data.extend;
data(data.count) := value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN comma_list_agr_type,
returnValue OUT varchar2,
flags IN number)
return number
is
l_data varchar2(32000);
begin
for x in ( select column_value from TABLE(data) order by 1 )
loop
l_data := l_data || ',' || x.column_value;
end loop;
returnValue := ltrim(l_data,',');
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT comma_list_agr_type,
ctx2 IN comma_list_agr_type)
return number
is
begin -- not really tested ;)
for i in 1 .. ctx2.data.count
loop
data.extend;
data(data.count) := ctx2.data(i);
end loop;
return ODCIConst.Success;
end;
end;
/
CREATE OR REPLACE FUNCTION comma_list(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING comma_list_agr_type;
/
GRANT EXECUTE ON COMMA_LIST to someuser
