如何在 SQL Server 2005 中对 sql 表进行尴尬的旋转?-数据库问题

How to make awkward pivot of sql table in SQL Server 2005?(如何在 SQL Server 2005 中对 sql 表进行尴尬的旋转?)

本文介绍了如何在 SQL Server 2005 中对 sql 表进行尴尬的旋转?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我必须从 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 | ValueType   | LabelNameOne | LabelNameTwo | LabelNameThree | LabelNameFour | LabelNameFive | LabelNameSix
---+-------------+--------------+--------------+----------------+---------------+---------------+--------------
1  | Numerator   | 41           | 0            | 21             | 15            |               |              
1  | Denominator | 10           | 0            | 10             | 10            |               |              
1  | Ratio       | 4,1          | 0            | 2,1            | 1,5           |               |              
2  | Numerator   | 19           | 0            | 15             |               | 19            | 17           
2  | Denominator | 19           | 0            | 16             |               | 19            | 17           
2  | Ratio       | 1            | 0            | 0,9375         |               | 1             | 1            
3  | Numerator   | 12           | 0            | 11             | 12            |               | 0            
3  | Denominator | 12           | 0            | 12             | 12            |               | 1            
3  | Ratio       | 1            | 0            | 0,9167         | 1             |               | 0

推荐答案

这应该可以解决您的问题.这真的是一个 UNPIVOT 和一个 PIVOT.请注意，您必须使数据保持一致，因为 UNPIVOT 会将所有数据放在同一列中.

This should sort you out. It's really an UNPIVOT and a PIVOT. Note that you have to conform your data because the UNPIVOT puts all the data in the same column.

请注意，我必须在内部动态 SQL 中重新创建/重新填充表变量 - 通常在处理永久表时不需要这样做.

Note that I had to recreate/repopulate the table variable in the inner dynamic SQL - typically this is not necessary when dealing with a permanent table.

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
    )

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

WITH    Labels
          AS (
              SELECT  DISTINCT
                        src.Label
                       ,ISNULL(label_order.Sort, 0) AS Sort
              FROM      @src AS src
              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
           ,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
             )
    SELECT  *
    FROM    UNPIVOTED PIVOT ( SUM(Val) FOR Label IN ({@pivot_cols}) ) AS pvt
    ORDER BY ID
           ,Col ;' ;

SET @template = REPLACE(@template, '{@pivot_cols}', @pivot_cols) ;

EXEC (@template) ;

这篇关于如何在 SQL Server 2005 中对 sql 表进行尴尬的旋转?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！