title: Combine two tables in one
body: <p>I have two tables in MSSQL Server 2012 and would like to combine them into one new table. They are linked by the column messageID. The first table (<em>Message</em>) has</p>
  
  <pre><code>messageID
  sender
  date
  </code></pre>
  
  <p>The second table(<em>recipientInfo</em>) has</p>
  
  <pre><code>messageID
  recipient
  </code></pre>
  
  <p>The main problem is that in <em>Message</em> the messageID is the primary key and thus unique. In <em>recipientInfo</em> the messageID is not unique (because one message can have multiple recipients). I'd like to have a new table with either</p>
  
  <ul>
  <li>One row per message, with recipients concatenated in one column</li>
  <li>Multiple rows per message, when there are multiple recipients (one recipient per row)</li>
  </ul>