1 Reply Latest reply: Nov 21, 2012 4:40 PM by 907388 RSS

    returning concatenated string for each acctid

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