Oracle：聚合连接分组结果中未分组列的方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1757394/
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
Oracle: Way to aggregate concatenate an ungrouped column in grouped results
提问by Rich
I have a query with several aggregate functions and then a few grouped columns. I want to take one of the grouped columns out of the group and perform some sort of aggregate "concatenating" of all the VARCHAR values it has. (Ideally in a new carriage separated list).
我有一个包含几个聚合函数和几个分组列的查询。我想从组中取出一个分组列，并对它拥有的所有 VARCHAR 值执行某种聚合“连接”。（理想情况下在一个新的马车分隔列表中）。
Here is my query and I note where I'd like to do this:
这是我的查询，我记下我想在哪里执行此操作：
SELECT rownum, F.*
FROM (SELECT
c.logical_type "MerchantType",
c.merchant_id "MerchantID",
c.m_name "MerchantName",
m.m_name "TransferredBy", /* <----- Make this aggregate */
SUM(DECODE(b.ba_price,null,0,DECODE(b.BILL_SRVC_ID,'CREDITCHANGE',0,b.ba_price))) "TotalValue",
sum(DECODE(b.ba_price,null,0,DECODE(b.BILL_SRVC_ID,'CREDITCHANGE',b.ba_price,0))) "LimitChange",
SUM(DECODE(b.ba_status,'bdone',1,0)) "TxnCount",
sum(to_number(decode(substr(b.ba_merchant_freetext,1,10),'Commission',substr(b.ba_merchant_freetext, 12,(instr(b.ba_merchant_freetext,';',1,1)-12))))) "Commission"
FROM bill_auth0 b,
merchant0 m,
merchant0 c
WHERE
b.srvc_prod_id = 'TRANSFER'
AND b.ba_channel = 'WPSS'
AND b.ba_status IN ('bdone')
AND b.merchant_id = m.merchant_id
AND b.customer_id = c.merchant_id
AND b.ba_timestamp BETWEEN to_date( '11/01/2009', 'MM/DD/YYYY' )
AND to_date( '11/17/2009', 'MM/DD/YYYY' )+1
GROUP BY
c.logical_type,
c.merchant_id,
c.m_name,
m.m_name /* <-- Remove from Grouped By */
ORDER BY c.logical_type, c.merchant_id, m.m_name) F;
So essentially I want to be able to have a result where "TransferredBy" would look something like:
Merchant1
Merchant2
Merchant3
所以基本上我希望能够得到“TransferredBy”看起来像这样的结果： Merchant1
Merchant2
Merchant3
if there were 3 seperate m.m_name matches to this Group Row.
如果有 3 个单独的 m.m_name 匹配到该组行。
回答by Egor Rogov
Here is a nice article about different string aggregation techniques.
这是一篇关于不同字符串聚合技术的好文章。
I can add yet another method (XML-based):
我可以添加另一种方法（基于 XML）：
select rtrim(
extract(
sys_xmlagg(
xmlelement("X",ename||', ')
),
'/ROWSET/X/text()'
).getstringval(),
', '
)
from emp;
And in 11g Release 2 we finally have built-in LISTAGGfunction.
在 11g 第 2 版中，我们终于内置了LISTAGG功能。
回答by Adam Paynter
Although I do not know of any built-in function capable of solving your problem, it appears that you can write your own aggregate functionthat can! Because I was curious, I tried my hand at implementing a custom, aggregate function that concatenates text with a delimiter:
虽然我不知道有什么内置函数可以解决您的问题，但您似乎可以编写自己的聚合函数！因为我很好奇，所以我尝试实现一个自定义的聚合函数，该函数将文本与分隔符连接起来：
The type spec:
类型规范：
CREATE OR REPLACE TYPE TextConcatenation AS OBJECT
(
text VARCHAR2(10000),
delimiter VARCHAR2(10),
concatenation_count NUMBER,
STATIC FUNCTION ODCIAggregateInitialize(actx IN OUT TextConcatenation) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT TextConcatenation, val IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN TextConcatenation, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT TextConcatenation, ctx2 IN TextConcatenation) RETURN NUMBER
)
The type body:
类型体：
CREATE OR REPLACE TYPE BODY TextConcatenation AS
STATIC FUNCTION ODCIAggregateInitialize(actx IN OUT TextConcatenation) RETURN NUMBER IS
BEGIN
IF actx IS NULL THEN
actx := TextConcatenation('', ', ', 0); #substitute your own delimiter here in the second argument
ELSE
actx.text := '';
actx.delimiter := ', '; # substitute your own delimiter here
actx.concatenation_count := 0;
END IF;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT TextConcatenation, val IN VARCHAR2) RETURN NUMBER IS
BEGIN
IF self.concatenation_count > 0 THEN
self.text := self.text || delimiter;
END IF;
self.text := self.text || val;
self.concatenation_count := self.concatenation_count + 1;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(self IN TextConcatenation, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
BEGIN
returnValue := text;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT TextConcatenation, ctx2 IN TextConcatenation) RETURN NUMBER IS
BEGIN
IF ctx2.concatenation_count > 0 THEN
IF self.concatenation_count > 0 THEN
self.text := self.text || delimiter || ctx2.text;
ELSE
self.text := ctx2.text;
END IF;
self.concatenation_count := self.concatenation_count + ctx2.concatenation_count;
END IF;
RETURN ODCIConst.Success;
END;
END;
The aggregate function:
聚合函数：
CREATE OR REPLACE FUNCTION text_concatenate(text VARCHAR2) RETURN VARCHAR2 AGGREGATE USING TextConcatenation;
After all this, I was able to execute the following query:
毕竟，我能够执行以下查询：
SELECT text_concatenate(name) FROM
(
SELECT 'Paynter' name FROM DUAL
UNION ALL
SELECT 'Adam' name FROM DUAL
)
The result was a single row:
结果是一行：
Paynter, Adam
回答by Ravi
use listagg http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions087.htmIt is well documented and supported.
使用 listagg http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions087.htm它有很好的文档和支持。
回答by jle
Try out wm_concat(yourColumn)... it is available depending on your db version. It is not an officially documented function, but does the same thing as many of the other functions listed above.
试试看wm_concat(yourColumn)……它是否可用取决于您的数据库版本。它不是正式记录的函数，但与上面列出的许多其他函数执行相同的操作。
This makes a comma separated list. To get the new line character you can surround it with replace()
这将生成一个逗号分隔的列表。要获得换行符，您可以用replace() 将其包围
Another tip is to use distinct within the wm_concat() such as wm_concat(distinct yourColumn)so that you don't get duplicates.
另一个技巧是在 wm_concat() 中使用 distinct ，例如wm_concat(distinct yourColumn) ，这样您就不会得到重复项。
