这应该可以解决您的问题。这实际上是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 @srcVALUES (1, 'LabelNameOne', 41, 10, 4.1) ;INSERT INTO @srcVALUES (1, 'LabelNameTwo', 0, 0, 0) ;INSERT INTO @srcVALUES (1, 'LabelNameThree', 21, 10, 2.1) ;INSERT INTO @srcVALUES (1, 'LabelNameFour', 15, 10, 1.5) ;INSERT INTO @srcVALUES (2, 'LabelNameOne', 19, 19, 1) ;INSERT INTO @srcVALUES (2, 'LabelNameTwo', 0, 0, 0) ;INSERT INTO @srcVALUES (2, 'LabelNameThree', 15, 16, 0.9375) ;INSERT INTO @srcVALUES (2, 'LabelNameFive', 19, 19, 1) ;INSERT INTO @srcVALUES (2, 'LabelNameSix', 17, 17, 1) ;INSERT INTO @srcVALUES (3, 'LabelNameOne', 12, 12, 1) ;INSERT INTO @srcVALUES (3, 'LabelNameTwo', 0, 0, 0) ;INSERT INTO @srcVALUES (3, 'LabelNameThree', 11, 12, 0.9167) ;INSERT INTO @srcVALUES (3, 'LabelNameFour', 12, 12, 1) ;INSERT INTO @srcVALUES (3, 'LabelNameSix', 0, 1, 0) ;INSERT INTO @label_orderVALUES ('LabelNameOne', 1) ;INSERT INTO @label_orderVALUES ('LabelNameTwo', 2) ;INSERT INTO @label_orderVALUES ('LabelNameThree', 3) ;INSERT INTO @label_orderVALUES ('LabelNameFour', 4) ;INSERT INTO @label_orderVALUES ('LabelNameFive', 5) ;INSERT INTO @label_orderVALUES ('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 @srcVALUES (1, ''LabelNameOne'', 41, 10, 4.1) ;INSERT INTO @srcVALUES (1, ''LabelNameTwo'', 0, 0, 0) ;INSERT INTO @srcVALUES (1, ''LabelNameThree'', 21, 10, 2.1) ;INSERT INTO @srcVALUES (1, ''LabelNameFour'', 15, 10, 1.5) ;INSERT INTO @srcVALUES (2, ''LabelNameOne'', 19, 19, 1) ;INSERT INTO @srcVALUES (2, ''LabelNameTwo'', 0, 0, 0) ;INSERT INTO @srcVALUES (2, ''LabelNameThree'', 15, 16, 0.9375) ;INSERT INTO @srcVALUES (2, ''LabelNameFive'', 19, 19, 1) ;INSERT INTO @srcVALUES (2, ''LabelNameSix'', 17, 17, 1) ;INSERT INTO @srcVALUES (3, ''LabelNameOne'', 12, 12, 1) ;INSERT INTO @srcVALUES (3, ''LabelNameTwo'', 0, 0, 0) ;INSERT INTO @srcVALUES (3, ''LabelNameThree'', 11, 12, 0.9167) ;INSERT INTO @srcVALUES (3, ''LabelNameFour'', 12, 12, 1) ;INSERT INTO @srcVALUES (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) ;
