sql - TSQL PIVOT多列

我有下表，但不确定是否可以旋转它并保留所有标签。

RATIO               RESULT   SCORE   GRADE
Current Ratio       1.294    60      Good
Gearing Ratio       0.3384   70      Good
Performance Ratio   0.0427   50      Satisfactory
TOTAL               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 SREC
PIVOT
(
  MAX(RESULT)
  FOR RATIO IN ([Current Ratio],[Gearing Ratio], [Performance Ratio], [TOTAL])
) AS PVT

结果如下:

Ratio    Current Ratio   Gearing Ratio   Performance Ratio
Result   1.294           0.3384          0.0427

我承认在下一步该怎么做才能产生所需的结果时感到很沮丧:

Ratio    Current Ratio   Gearing Ratio   Performance Ratio   TOTAL
Result   1.294           0.3384          0.0427              NULL
Score    60              70              50                  180
Grade    Good            Good            Satisfactory        Good

最佳答案

由于您要透视多个数据列，因此我首先建议不要透视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 with Demo。一旦数据被取消透视，就可以应用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 with Demo。这将为您提供结果:

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

关于sql - TSQL PIVOT多列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19590799/