Question

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;

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

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

Answer 1

Here is a nice article about different string aggregation techniques.

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

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

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

Answer 2

Although I do not know of any built-in function capable of solving your problem, it appears that you can write your own aggregate function that 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

Answer 3

use listagg http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions087.htm It is well documented and supported.

Answer 4

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.

This makes a comma separated list. To get the new line character you can surround it with replace()

Another tip is to use distinct within the wm_concat() such as wm_concat(distinct yourColumn) so that you don t get duplicates.

友情链接