text: Q:
  
  How to make additional columns on pivot of sql table in SQL Server 2005?
  
  After getting such a great feedback from my last question. Maybe someone can help me with this problem also.
  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?
  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.
  Given Data
  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     
  
  Wanted result
  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              | ...
  
  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.
  Also if you need the sample data in a more SQL way, you can try this one:
  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) ;
  
  A:
  
  You need to combine the labels before the PIVOT.  The column list also needs to take account of the cross join of label possibilities:
  SET NOCOUNT ON ; 
  
  DECLARE @pivot_cols AS varchar(max) ; 
  DECLARE @src AS TABLE 
      ( 
       ID int NOT NULL 
      ,Label varchar(14) NOT NULL 
      ,Numerator int NOT NULL 
      ,Denominator int NOT NULL 
      ,Ratio decimal(5, 4) NOT NULL 
      ) ; 
  
  DECLARE @label_order AS TABLE 
      ( 
       Label varchar(14) NOT NULL 
      ,Sort int NOT NULL 
      ) 
  
  DECLARE @sub_label_order AS TABLE 
      ( 
       SubLabel varchar(14) NOT NULL 
      ,Sort int 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) ; 
  
  INSERT  INTO @label_order 
  VALUES  ('LabelNameOne', 1) ; 
  INSERT  INTO @label_order 
  VALUES  ('LabelNameTwo', 2) ; 
  INSERT  INTO @label_order 
  VALUES  ('LabelNameThree', 3) ; 
  INSERT  INTO @label_order 
  VALUES  ('LabelNameFour', 4) ; 
  INSERT  INTO @label_order 
  VALUES  ('LabelNameFive', 5) ; 
  INSERT  INTO @label_order 
  VALUES  ('LabelNameSix', 6) ; 
  
  INSERT  INTO @sub_label_order 
  VALUES  ('Numerator', 1) ; 
  INSERT  INTO @sub_label_order 
  VALUES  ('Denominator', 2) ; 
  INSERT  INTO @sub_label_order 
  VALUES  ('Ratio', 3) ; 
  
  WITH    Labels 
            AS ( 
                SELECT  DISTINCT 
                          src.Label + sublabel.SubLabel AS Label
                         ,ISNULL(label_order.Sort, 0) AS Sort 
                         ,ISNULL(sublabel.Sort, 0) AS SubSort 
                FROM      @src AS src 
                CROSS JOIN @sub_label_order AS sublabel
                LEFT JOIN @label_order AS label_order 
                          ON src.label = label_order.label 
               ) 
      SELECT  @pivot_cols = COALESCE(@pivot_cols + ',', '') + QUOTENAME(Label, '[') 
      FROM    Labels 
      ORDER BY Sort 
             ,SubSort
             ,Label ; 
  
  DECLARE @template AS varchar(max) ; 
  
  SET @template = ' 
  DECLARE @src AS TABLE 
      ( 
       ID int NOT NULL 
      ,Label varchar(14) NOT NULL 
      ,Numerator int NOT NULL 
      ,Denominator int NOT NULL 
      ,Ratio decimal(5, 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) ; 
  
  WITH    src_conformed 
            AS ( 
                SELECT    ID 
                         ,Label 
                         ,CAST (Numerator AS decimal(10, 4)) AS Numerator 
                         ,CAST (Denominator AS decimal(10, 4)) AS Denominator 
                         ,CAST (Ratio AS decimal(10, 4)) AS Ratio 
                FROM      @src 
               ), 
          UNPIVOTED 
            AS ( 
                SELECT    * 
                FROM      src_conformed UNPIVOT ( Val FOR Col IN (Numerator, Denominator, Ratio) ) AS unpvt 
               )
               ,COMBINED AS (SELECT  ID, Label + Col AS NewLabel, Val
      FROM    UNPIVOTED)
      SELECT *
      FROM    COMBINED PIVOT ( SUM(Val) FOR NewLabel IN ({@pivot_cols}) ) AS pvt 
       ORDER BY ID ;' ; 
  
  SET @template = REPLACE(@template, '{@pivot_cols}', @pivot_cols) ; 
  
  EXEC (@template) ;
  
  