title: SSIS - transform 2 records from table A to 1 record in table B
body: <p>I have following data in employee Table A:</p>
  
  <pre><code>ID  | emp | City_Type  | City
  1   | 101 | Z          | Tokyo
  2   | 101 | Y          | New York
  </code></pre>
  
  <p><code>City_Type</code> can either be Y or Z. Y being the city this person was born in, Z is the city he/she is living now. 
  I need to put these together in a table 'B' which look like the folowing:</p>
  
  <pre><code>ID  | emp | Current_City  | Birth_City
  </code></pre>
  
  <p>So in the end, Table B must be filled like this:</p>
  
  <pre><code>ID  | emp | Current_City  | Birth_City
  1   | 101 | Tokyo         | New York
  </code></pre>
  
  <p>(in some cases, one of the 2 can be empty/null)</p>
  
  <p>Any suggestions on how to do this? I haven't been able to found much information on this myself.</p>