TSQL PIVOT多列

扫码查看
本文介绍了TSQL PIVOT多列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

I have the following table but unsure of whether it is possible to pivot this and retain all the labels.

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

我会承认对枢轴的使用不是很好,因此经过几次尝试导致此输出:

I will admit to not being very good with the use of pivots, so after several attempts resulting in this output:

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

我将对下一步该如何做才能产生所需的结果感到很沮丧:

I will admit to be feeling very stumped on what to do next to produce the result I need which is:

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

推荐答案

由于您要透视多个数据列,因此我建议先取消透视resultscoregrade列,这样您就不必有多列,但您将有多行.

Since you want to pivot multiple columns of data, I would first suggest unpivoting the result, score and grade columns so you don't have multiple columns but you will have multiple rows.

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

Depending on your version of SQL Server you can use the UNPIVOT function or CROSS APPLY. The syntax to unpivot the data will be similar to:

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提琴.数据一旦被取消透视,便可以应用PIVOT功能:

See SQL Fiddle with Demo. Once the data has been unpivoted, then you can apply the PIVOT function:

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提琴.这将为您提供结果:

See SQL Fiddle with Demo. This will give you the result:

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

这篇关于TSQL PIVOT多列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 19:50
查看更多