title: How to make additional columns on pivot of sql table in SQL Server 2005?
body: <p>After getting such a great feedback from <a href="https://stackoverflow.com/questions/2433533/how-to-make-awkward-pivot-of-sql-table-in-sql-server-2005">my last question</a>. Maybe someone can help me with this problem also.</p>
  
  <p>I have to rotate a given table from an SQL Server but a normal pivot just doesn't work (as far as i tried). So has anybody an idea how to rotate the table into the desired format?</p>
  
  <p>Just to make the problem more complicated, the list of given labels can vary and it is possible that a new label name can come into at any given time.</p>
  
  <h3>Given Data</h3>
  
  <pre><code>ID |  Label          |  Numerator  |  Denominator  |  Ratio 
  ---+-----------------+-------------+---------------+--------
  1  |  LabelNameOne   |  41         |  10           |  4,1   
  1  |  LabelNameTwo   |  0          |  0            |  0     
  1  |  LabelNameThree |  21         |  10           |  2,1   
  1  |  LabelNameFour  |  15         |  10           |  1,5   
  2  |  LabelNameOne   |  19         |  19           |  1     
  2  |  LabelNameTwo   |  0          |  0            |  0     
  2  |  LabelNameThree |  15         |  16           |  0,9375
  2  |  LabelNameFive  |  19         |  19           |  1     
  2  |  LabelNameSix   |  17         |  17           |  1     
  3  |  LabelNameOne   |  12         |  12           |  1     
  3  |  LabelNameTwo   |  0          |  0            |  0     
  3  |  LabelNameThree |  11         |  12           |  0,9167
  3  |  LabelNameFour  |  12         |  12           |  1     
  3  |  LabelNameSix   |  0          |  1            |  0     
  </code></pre>
  
  <h3>Wanted result</h3>
  
  <pre><code>ID | LabelNameOneNumerator | LabelNameOneDenominator | LabelNameOneRatio | LabelNameTwoNumerator | LabelNameTwoDenominator | LabelNameTwoRatio | LabelNameThreeNumerator | LabelNameThreeDenominator | LabelNameThreeRatio | ...
  ---+-----------------------+-------------------------+-------------------+-----------------------+-------------------------+-------------------+-------------------------+---------------------------+---------------------+-----
  1  | 41                    | 10                      | 4,1               | 0                     | 0                       | 0                 | 21                      | 10                        | 2,1                 | ...
  2  | 19                    | 19                      | 1                 | 0                     | 0                       | 0                 | 15                      | 16                        | 0,9375              | ...
  3  | 12                    | 12                      | 1                 | 0                     | 0                       | 0                 | 11                      | 12                        | 0,9167              | ...
  </code></pre>
  
  <p>I know, after getting such a good answer for my previous question i should be able to solve this problem on myself, but i just can't get my head around this pivot, unpivot part.</p>
  
  <p>Also if you need the sample data in a more SQL way, you can try this one:</p>
  
  <pre><code>DECLARE @src AS TABLE
      (
       ID int NOT NULL
      ,Label varchar(14) NOT NULL
      ,Numerator int NOT NULL
      ,Denominator int NOT NULL
      ,Ratio decimal(10, 4) NOT NULL
      ) ;
  
  INSERT  INTO @src
  VALUES  (1, 'LabelNameOne', 41, 10, 4.1) ;
  INSERT  INTO @src
  VALUES  (1, 'LabelNameTwo', 0, 0, 0) ;
  INSERT  INTO @src
  VALUES  (1, 'LabelNameThree', 21, 10, 2.1) ;
  INSERT  INTO @src
  VALUES  (1, 'LabelNameFour', 15, 10, 1.5) ;
  INSERT  INTO @src
  VALUES  (2, 'LabelNameOne', 19, 19, 1) ;
  INSERT  INTO @src
  VALUES  (2, 'LabelNameTwo', 0, 0, 0) ;
  INSERT  INTO @src
  VALUES  (2, 'LabelNameThree', 15, 16, 0.9375) ;
  INSERT  INTO @src
  VALUES  (2, 'LabelNameFive', 19, 19, 1) ;
  INSERT  INTO @src
  VALUES  (2, 'LabelNameSix', 17, 17, 1) ;
  INSERT  INTO @src
  VALUES  (3, 'LabelNameOne', 12, 12, 1) ;
  INSERT  INTO @src
  VALUES  (3, 'LabelNameTwo', 0, 0, 0) ;
  INSERT  INTO @src
  VALUES  (3, 'LabelNameThree', 11, 12, 0.9167) ;
  INSERT  INTO @src
  VALUES  (3, 'LabelNameFour', 12, 12, 1) ;
  INSERT  INTO @src
  VALUES  (3, 'LabelNameSix', 0, 1, 0) ;
  </code></pre>