title: Pivot multiple columns based on one column in SQL Server
body: <p>I have the following source and destination tables in SQL Server 2008R2. How can I do pivot(s) in TSQL to transform <code>SourceTbl</code> into <code>DestTbl</code>? Hoping that the empIndex will somehow help in the pivot.</p>
  
  <p><strong>SourceTbl</strong></p>
  
  <pre><code>empId    empIndex    empState    empStDate    empEndDate
  ========================================================
  10        1           AL          1/1/2012     12/1/2012
  10        2           FL          2/1/2012     2/1/2013
  15        1           FL          3/20/2012    1/1/2099
  </code></pre>
  
  <p><strong>DestTbl</strong></p>
  
  <pre><code>empId    empState1  empState1StDate    empState1EndDt    empState2  empState2StDate    empState2EndDt
  =========================================================================================================
  10        AL         1/1/2012           12/1/2012         FL         2/1/2012           2/1/2013
  15        FL         3/20/2012          1/1/2099          NULL       NULL               NULL
  </code></pre>