我必须从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 | 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将所有数据放在同一列中。
请注意，我必须在内部动态SQL中重新创建/重新填充表变量-通常，在处理永久表时，这不是必需的。
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) ;
