TSQL PIVOT多列

Question

我有下表，但不确定是否可以旋转它并保留所有标签。RATIO               RESULT   SCORE   GRADECurrent Ratio       1.294    60      GoodGearing Ratio       0.3384   70      GoodPerformance Ratio   0.0427   50      SatisfactoryTOTAL               NULL     180     Good我会承认对枢轴的使用不是很好，因此经过几次尝试导致此输出：SELECT 'RESULT' AS 'Ratio'  ,[Current Ratio] AS 'Current Ratio'  ,[Gearing Ratio] AS 'Gearing Ratio'  ,[Performance Ratio] AS 'Performance Ratio'  ,[TOTAL] AS 'TOTAL'FROM(  SELECT RATIO, RESULT   FROM GRAND_TOTALS) AS SRECPIVOT (  MAX(RESULT)   FOR RATIO IN ([Current Ratio],[Gearing Ratio], [Performance Ratio], [TOTAL])) AS PVT结果如下：Ratio    Current Ratio   Gearing Ratio   Performance RatioResult   1.294           0.3384          0.0427我承认在下一步该怎么做才能产生所需的结果时感到很沮丧：Ratio    Current Ratio   Gearing Ratio   Performance Ratio   TOTALResult   1.294           0.3384          0.0427              NULLScore    60              70              50                  180Grade    Good            Good            Satisfactory        Good

Answer 1

由于要转动多列数据，我会首先建议unpivoting的result，score并且grade列，所以你不必多列，但你将有多个行。

根据您的SQL Server版本，您可以使用UNPIVOT函数或CROSS APPLY。取消数据透视的语法类似于：

select ratio, col, value

from GRAND_TOTALS

cross apply

(

  select 'result', cast(result as varchar(10)) union all

  select 'score', cast(score as varchar(10)) union all

  select 'grade', grade

) c(col, value)

请参阅带有演示的SQL Fiddle。一旦数据被取消透视，就可以应用PIVOT功能：

select ratio = col,

  [current ratio], [gearing ratio], [performance ratio], total

from

(

  select ratio, col, value

  from GRAND_TOTALS

  cross apply

  (

    select 'result', cast(result as varchar(10)) union all

    select 'score', cast(score as varchar(10)) union all

    select 'grade', grade

  ) c(col, value)

) d

pivot

(

  max(value)

  for ratio in ([current ratio], [gearing ratio], [performance ratio], total)

) piv;

请参阅带有演示的SQL Fiddle。这将为您提供结果：

|  RATIO | CURRENT RATIO | GEARING RATIO | PERFORMANCE RATIO |     TOTAL |

|--------|---------------|---------------|-------------------|-----------|

|  grade |          Good |          Good |      Satisfactory |      Good |

| result |       1.29400 |       0.33840 |           0.04270 |    (null) |

|  score |      60.00000 |      70.00000 |          50.00000 | 180.00000 |