title: SSIS Combining multiple rows into a single row with multiple columns
body: <p>I have a table that does not have a primary key, but has an ID field that could be used as a primary key.  I would like to transform this table into something useable to track inserts, updates, and deletes. Just adding a Identity column is not a viable solution because the table gets recreated every evening.</p>
  
  <p>The table looks something like:</p>
  
  <pre><code>ID       Specialty  
  --       ---------  
  01       Fam Med  
  02       Fam Med  
  02       Int Med  
  03       Surgery  
  03       Thor Sur  
  03       Card Sur  
  04       Undersea
  </code></pre>
  
  <p>I would like to transform it into something like the following and use ID as the primary key:</p>
  
  <pre><code>ID    Specialty1    Specialty2   Specialty3
  --    ----------    ----------   ----------
  01    Fam Med
  02    Fam Med       Int Med
  03    Surgery       Thor Sur     Card Sur
  04    Undersea
  </code></pre>
  
  <p>I was leaning toward using the SQL pivot operator, but I am not familiar enough with pivots and I am not sure it is appropriate in this case.</p>
  
  <p>Thank you in advance for your input.</p>