This discussion is archived
1 Reply Latest reply: Nov 21, 2012 2:40 PM by 907388 RSS

returning concatenated string for each acctid

907388 Newbie
Currently Being Moderated
Hi All,

For each acct id, bank statement can be sent to multiple CC EMAIL addresses.

So, for each acct - only one row should appear. I would need to string together cc email addreses for each acct.

Can someone provide code for a function that takes acct_id and loops through all the CC email addresses and for each ACCT_ID passed and returns a concatenated result of
email addresses like "email1, email 2".

GIVEN TABLE:

ACCT EMAIL ADDRESS:
ACCT1 abc@good.com
ACCT1 cvg@good.com

RESULT in report:


ACCT1 abc@good.com, cvg@good.com

Need to do it asap.

Regards,
  • 1. Re: returning concatenated string for each acctid
    Solomon Yakobson Guru
    Currently Being Moderated
    Version? If you are on 11.2 use LISTAGG:
    select  acct,
            listagg(email_address,', ') within group(order by email_address) email_address_list
      from  ypour_table
      group by acct
    /
    For example:
    with sample_table as (
                          select 'ACCT1' ACCT,'abc@good.com' EMAIL_ADDRESS from dual union all
                          select 'ACCT1','cvg@good.com' from dual
                         )
    select  acct,
            listagg(email_address,', ') within group(order by email_address) email_address_list
      from  sample_table
      group by acct
    /
    
    ACCT  EMAIL_ADDRESS_LIST
    ----- ------------------------------
    ACCT1 abc@good.com, cvg@good.com
    
    SQL> 
    On lower versions:
    select  acct,
            rtrim(xmlagg(xmlelement(e,email_address,', ').extract('//text()') order by email_address),', ') email_address_list
      from  your_table
      group by acct
    /
    For example:
    with sample_table as (
                          select 'ACCT1' ACCT,'abc@good.com' EMAIL_ADDRESS from dual union all
                          select 'ACCT1','cvg@good.com' from dual
                         )
    select  acct,
            rtrim(xmlagg(xmlelement(e,email_address,', ').extract('//text()') order by email_address),', ') email_address_list
      from  sample_table
      group by acct
    /
    
    ACCT  EMAIL_ADDRESS_LIST
    ----- ------------------------------
    ACCT1 abc@good.com, cvg@good.com
    
    SQL>
    SY.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points