title: SQL Server array_agg (master - detail)
body: <p>How can I convert this PostgreSQL code to SQL Server ?</p>
  
  <pre><code>select 
      countries.title, 
      (select array_to_json(array_agg(row_to_json(t))) 
       from postcodes t 
       where t.country_id = countries.id) as codes 
  from countries
  </code></pre>
  
  <p>My initial problem is that I need to select complete master table and with each row all details.</p>
  
  <p><strong>Countries</strong>:</p>
  
  <pre><code>id    title
  1     SLO
  2     AUT
  </code></pre>
  
  <p><strong>PostCodes</strong>:</p>
  
  <pre><code>id  country_id  code    title
  1   1           1000    Lj
  2   1           2000    Mb
  3   2           22180   Vi
  4   2           22484   De
  </code></pre>
  
  <p>Desired result:</p>
  
  <pre><code>1  SLO  1000;Lj|2000;MB
  2  AUT  22180;Vi|22484;De
  </code></pre>
  
  <p>Not:</p>
  
  <pre><code>1  SLO  1000  Lj
  1  SLO  2000  Mb
  2  AUT  22180 Vi
  2  AUT  22484 De
  </code></pre>
  
  <p>The best solution would be using <a href="https://msdn.microsoft.com/en-us/library/dn921882.aspx" rel="nofollow noreferrer">FOR JSON</a>, but unfortunately I need support for 2008 or at least 2012.</p>
  
  <p>With left join all master data are duplicated for detail count, but I do not want to do this. Even worse it would be to select all countries and then call select on <code>post_codes</code> for every country in for loop.</p>