您的位置: 首页  >  技术问答  >  如何在SQL Server 2005中的sql表的枢轴上添加其他列?

如何在SQL Server 2005中的sql表的枢轴上添加其他列?

分类: 技术问答 • 2022-04-26 15:58:09
问题描述：

在

After getting such a great feedback from my last question. Maybe someone can help me with this problem also.

我必须从SQL Server旋转给定的表，但正常的枢轴是行不通的(就我所尝试的而言).那么有人知道如何将表格旋转为所需的格式吗?

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.

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

我知道，在为之前的问题提供了很好的答案之后，我应该能够自己解决这个问题，但是我只是无法绕开这个枢纽，枢纽部分.

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.

如果您还需要更多SQL方式的示例数据，则可以尝试以下一种方法:

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

答

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

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

相关推荐