如何在SQL Server 2005中的sql表的pivot上添加附加列？

Question

在从 我的最后一个问题。也许有人也可以帮助我解决这个问题。

我必须从 SQL Server 旋转给定的表，但普通的枢轴不起作用（据我尝试）。那么有人知道如何将表格旋转为所需的格式吗？

只是为了使问题更加复杂，给定标签的列表可能会有所不同，并且可能在任何给定时间出现新的标签名称。

鉴于我知道所需的数据

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     

结果

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              | ...

，在为我之前的问题得到如此好的答案后，我应该能够自己解决这个问题，但我就是无法理解这个枢轴、非枢轴部分。

另外，如果您需要更多 SQL 方式的示例数据，您可以尝试以下方法：

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) ;

Answer 1

您需要在 PIVOT 之前合并标签。列列表还需要考虑标签交叉连接的可能性：

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) ;