title: SQL Server UNPIVOT table by combining two rows based on condition, using a SINGLE unpivot
body: <p><strong>I have a big query which gives a table result looking like this:</strong></p>
  
  <pre><code>[Date]          [Type]  TC1   TC2    TC3
  ..........................................
  '2014-01-01'    T       11    22     33
  '2014-01-01'    C       44    55     66
  '2014-01-02'    T       111   222    333
  '2014-01-02'    C       444   555    666
  '2014-01-03'    T       1111  2222   3333
  '2014-01-04'    C       4444  5555   6666
  </code></pre>
  
  <p><strong>I want to make it look like this:</strong></p>
  
  <pre><code>[Date]          Cntr      TValue      CValue
  ............................................
  '2014-01-01'    TC1       11          44
  '2014-01-01'    TC2       22          55
  '2014-01-01'    TC3       33          66
  '2014-01-02'    TC1       111         444
  '2014-01-02'    TC2       222         555
  '2014-01-02'    TC3       333         666
  '2014-01-03'    TC1       1111        4444
  '2014-01-03'    TC2       2222        5555
  '2014-01-03'    TC3       3333        6666
  </code></pre>
  
  <p><strong>I managed to do this by unpivoting two times and then join the results.
  The query I made is this</strong>:</p>
  
  <pre><code>SELECT A.[Date],A.Cntr,A.TValue,B.CValue
  FROM
  (
      SELECT [Date],Cntr,TValue
      FROM TB 
      UNPIVOT
      (
      TValue
      FOR Cntr IN (TC1,TC2,TC3)
      ) u
      WHERE  [Type] = 'T' AND u.[Type] = 'T'
  ) A
  JOIN
  (
      SELECT [Date],Cntr,CValue
      FROM TB 
      UNPIVOT
      (
      CValue
      FOR Cntr IN (TC1,TC2,TC3)
      ) u
      WHERE  [Type] = 'C' AND u.[Type] = 'C'
  ) B
  ON B.[Date] = A.[Date] AND A.[Cntr] = B.[Cntr]
  </code></pre>
  
  <p>My problem is that TB table comes from a big and time consuming query and, given the fact that it will be called thousands of times during my stored procedure, I dont want to waste precious time by inserting its many rows in a temporary table. </p>
  
  <p><strong>Is there a way to solve this by using a single unpivot, or using a better approach ?</strong>
  Also, this has to run on <strong>SQLServer >= 2005</strong></p>
  
  <p>SqlFiddle <a href="http://sqlfiddle.com/#!3/1f4bf/1" rel="nofollow">here</a> </p>
  
  <p>Thank you!</p>