title: SQL Combine multiple rows into one with multiple columns
body: <p>I've got multiple rows in my result from my query:</p>
  
  <p>for example, the table "Address":</p>
  
  <pre><code>Street | Number | City
  ----------------------
  A1     | A2     | A3
  B1     | B2     | B3
  </code></pre>
  
  <p>What I actually want is:</p>
  
  <pre><code>Address1_Street | Address1_Number | Address1_City | Address2_Street | Address2_Number | Address2_City
  ------------------------------------------------------------------------------------------------------
  A1              | A2              | A3            | B1              | B2              | B3
  </code></pre>
  
  <p>Anyone who knows how I can achieve this?</p>
  
  <p>I've managed to get to this point now (sorry for using other columns, the one above was an example, but I'll guess you'll get the point):</p>
  
  <pre><code>select distinct
      a.ID,
      a.Name, 
      ca1.NameLine1 as Address1_NameLine1, 
      ca2.NameLine1 as Address2_NameLine1
  from 
      dbo.Accounts a, 
      dbo.Addresses ca1,
      dbo.Addresses ca2
  where 
          (a.ID = ca1.AccountID AND a.ID = ca2.AccountID)
      AND (a.Name = 'TEST')
      AND (ca1.ID &lt;&gt; ca2.ID)
  </code></pre>
  
  <p>But I'm still getting 2 rows... where Address1 switches with Address2. Anyone who knows how to only get one? Thanks!</p>