title: PostgreSQL 9.1: How to concatenate rows in array without duplicates, JOIN another table
body: <p>I am using PostgreSQL 9.1 and need help with concatenating multiple rows in one. I need to do that in 2 tables. When I use two times <code>array_agg()</code> functions I get duplicated values in result. </p>
  
  <p>Tables:</p>
  
  <pre><code>CREATE TABLE rnp (id int, grp_id int, cabinets varchar(15) );
  
  INSERT INTO rnp VALUES
   (1,'11','cabs1')
  ,(2,'11','cabs2')
  ,(3,'11','cabs3')
  ,(4,'11','cabs4')
  ,(5,'22','c1')
  ,(6,'22','c2');
  
  CREATE TABLE ips (id int, grp_id int, address varchar(15));
  
  INSERT INTO ips VALUES
   (1,'11','NY')
  ,(2,'11','CA')
  ,(3,'22','DC')
  ,(4,'22','LA');
  </code></pre>
  
  <p>SQL:</p>
  
  <pre><code>SELECT DISTINCT
  
    rnp.grp_id,
    array_to_string(array_agg(rnp.cabinets)OVER (PARTITION BY rnp.grp_id), ',') AS cabinets,
    array_to_string(array_agg(ips.address) OVER (PARTITION BY ips.grp_id), ',') AS addresses
  
  
  FROM rnp JOIN ips ON rnp.grp_id=ips.grp_id
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>GRP_ID  CABINETS                                             ADDRESSES
  11  cabs1,cabs1,cabs2,cabs2,cabs3,cabs3,cabs4,cabs4     NY,CA,NY,CA,NY,CA,NY,CA
  22  c1,c1,c2,c2                                             DC,LA,DC,LA
  </code></pre>
  
  <p><strong>And what I need is:</strong></p>
  
  <pre><code> GRP_ID     CABINETS                 ADDRESSES
      11  cabs1,cabs2,cabs3,cabs4       NY,CA,
      22  c1,c2                         DC,LA
  </code></pre>
  
  <p>This example in SQLFiddle: <a href="http://sqlfiddle.com/#!1/4815e/19" rel="noreferrer">http://sqlfiddle.com/#!1/4815e/19</a></p>
  
  <p>There is no problem if use one table  - SQLFiddle: <a href="http://sqlfiddle.com/#!1/4815e/20" rel="noreferrer">http://sqlfiddle.com/#!1/4815e/20</a></p>
  
  <p>What am I missing? Is it possible to do this, because of JOIN?</p>