title: Using distinct in XMLAGG on duplicating records
body: <p>I am facing a problem using XMLAGG in sql.</p>
  
  <p>I have a table with multiple records which can be repeated. the Table contains Customer Addresses and Customer Name.</p>
  
  <pre><code>Create Table cust_data(
      cust_name varchar2(30),
      cust_addr_line1 varchar2(300),
      cust_addr_line2 varchar2(300),
      cust_addr_line3 varchar2(300),
      cust_addr_type varchar2(3));
  </code></pre>
  
  <p>The table may contain multiple records for a Single Customer Name and Different Address Types.</p>
  
  <p>Also, a single customer may have multiple addresses of the same type also.</p>
  
  <p>so a customer may have addresses like</p>
  
  <pre><code>cust1 address1 curr_address
  cust1 address2 old_address
  cust1 address3 old_address
  cust1 address4 old_address
  cust2 address5 curr_address
  cust2 address6 old_address
  </code></pre>
  
  <p>I have a select where i want to take out all the customer names with old_addresses in a comma separated format.</p>
  
  <p>using the same i used the following sql</p>
  
  <pre><code> select XMLAGG(XMLELEMENT(E, cust_name || ',')).EXTRACT('//text()')
   from cust_data where cust_addr_type ='old_address';
  </code></pre>
  
  <p>I get the following output:</p>
  
  <blockquote>
    <p>cust1,cust1,cust1,cust2,</p>
  </blockquote>
  
  <p>how do get the output as </p>
  
  <blockquote>
    <p>cust1,cust2</p>
  </blockquote>
  
  <p>Please help.</p>
  
  <p>Edit#1:</p>
  
  <p>The other tables can be taken like this:</p>
  
  <pre><code>Create Table cust_info(
      cust_name varchar2(30),
      Cust_account varchar2(300),
      cust_amount_paid varchar2(300),
      cust_amount_pend varchar2(300),
      cust_payment_type varchar2(300));
  
  Create Table payment_master_info(
      pmnt_type varchar2(30),
      pmnt_desc varchar2(300),
      pmnt_rate varchar2(300),
      pmnt_tenure varchar2(300));
  </code></pre>
  
  <p>The query is like this:</p>
  
  <pre><code>SELECT XMLAGG(XMLELEMENT(E, CUST_NAME || ',')) .EXTRACT('//text()'),
               CD.CUST_ADDR_LINE1,
               CD.CUST_ADDR_LINE2,
               CD.CUST_ADDR_LINE3,
               CI.CUST_AMOUNT_PAID,
               CI.CUST_AMOUNT_PEND,
               CI.CUST_ACCOUNT
      FROM CUST_INFO CI, PAYMENT_MASTER_INFO PM, CUST_DATA CD
   WHERE CD.CUST_NAME = CI.CUST_NAME
       AND CI.CUST_PAYMENT_TYPE = PM.PMNT_TYPE
       AND CUST_ADDR_TYPE = 'old_address';
  </code></pre>
  
  <p>Now the data in this huge. the data in the pmnt_type ranges from 10000-15000 data
  and in other tables it ranges from 2100000-5000000</p>
  
  <p>If I apply inner queries for distinct data, the performance drops exponentially.</p>
  
  <p>is there any other way?</p>
  
  <p>Edit#2: Also while using this inner query, during executing, i getting an ORA-19011(: Character string buffer too small.) error. can there be any reason why...??</p>