Oracle:聚合组合分组结果中未分组列的方法 [英] Oracle: Way to aggregate concatenate an ungrouped column in grouped results

问题描述

我有一个查询，其中包含几个聚合函数，然后是几个分组的列.我想从组中取出分组的列之一，并对它具有的所有VARCHAR值执行某种聚合的连接". (理想情况下是在新的分隔符列表中.)

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).

这是我的查询，我在这里指出了要执行的操作:

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;

所以从本质上讲，我希望能够有一个结果，其中"TransferredBy"看起来像这样: 商家1
商家2
商家3

So essentially I want to be able to have a result where "TransferredBy" would look something like: Merchant1
Merchant2
Merchant3

如果与此组行有3个单独的m.m_name匹配项.

if there were 3 seperate m.m_name matches to this Group Row.

推荐答案

这是一篇有关不同字符串聚合技术.

我可以添加另一种方法(基于XML):

I can add yet another method (XML-based):

select rtrim(
         extract(
           sys_xmlagg(
             xmlelement("X",ename||', ')
           ),
           '/ROWSET/X/text()'
         ).getstringval(),
         ', '
       )
  from emp;

在11g第2版中，我们终于有了内置的

And in 11g Release 2 we finally have built-in LISTAGG function.

这篇关于Oracle:聚合组合分组结果中未分组列的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！