时间戳

首页 > 解决方案 > 在 SQL Server 中使用 TSQL 透视和反透视数据

sql - 在 SQL Server 中使用 TSQL 透视和反透视数据

问题描述

Report_Gap  Report_Group            Name            Percentage_Difference
1 Year      ABC                     Operating       3.9
1 Year      ABC                     Other           6.8
1 Year      DEF                     Other           5.7
1 Year      DEF                     Operating       1.2
1 Year      GHI                     Operating       6.5
1 Year      GHI                     Other           -2.0
5 Year      ABC                     Operating       13.6
5 Year      ABC                     Other           5.4
5 Year      DEF                     Other           5.5
5 Year      DEF                     Operating       15.4
5 Year      GHI                     Operating       19.5
5 Year      GHI                     Other           -7.9

使用 Transact-SQL，上述格式的数据是否可以表示如下？

Report_Gap  Report_Group    Operating   Other
1 Year      ABC             3.9         6.8 
1 Year      DEF             1.2         5.7
1 Year      GHI             6.5         -2.0
5 Year      ABC             13.6        5.4
5 Year      DEF             15.4        5.5 
5 Year      GHI             19.5        -7.9

我已经建立了数据，这样您就不必这样做了。

with MyData
as
(
select '1 Year' as Report_Gap,
    'ABC' as Report_Group,
    'Operating' as Name,
    3.9 as Percentage_Difference
union all
select '1 Year' as Report_Gap,
    'ABC' as Report_Group,
    'Other' as Name,
    6.8 as Percentage_Difference
union all
select '1 Year' as Report_Gap,
    'DEF' as Report_Group,
    'Other' as Name,
    5.7 as Percentage_Difference
union all
select '1 Year' as Report_Gap,
    'DEF' as Report_Group,
    'Operating' as Name,
    1.2 as Percentage_Difference
union all
select '1 Year' as Report_Gap,
    'GHI' as Report_Group,
    'Operating' as Name,
    6.5 as Percentage_Difference
union all
select '1 Year' as Report_Gap,
    'GHI' as Report_Group,
    'Other' as Name,
    -2.0 as Percentage_Difference
union all
select '5 Year' as Report_Gap,
    'ABC' as Report_Group,
    'Operating' as Name,
    13.6 as Percentage_Difference
union all
select '5 Year' as Report_Gap,
    'ABC' as Report_Group,
    'Other' as Name,
    5.4 as Percentage_Difference
union all
select '5 Year' as Report_Gap,
    'DEF' as Report_Group,
    'Other' as Name,
    5.5 as Percentage_Difference
union all
select '5 Year' as Report_Gap,
    'DEF' as Report_Group,
    'Operating' as Name,
    15.4 as Percentage_Difference
union all
select '5 Year' as Report_Gap,
    'GHI' as Report_Group,
    'Operating' as Name,
    19.5 as Percentage_Difference
union all
select '5 Year' as Report_Gap,
    'GHI' as Report_Group,
    'Other' as Name,
    -7.9 as Percentage_Difference
)
select * from MyData
go

标签： sqlsql-servertsql

解决方案

你想要有条件的聚合：

SELECT Report_Gap, Report_Group, 
       MAX(CASE WHEN Name = 'Operating' THEN Percentage_Difference END) AS Operating,
       MAX(CASE WHEN Name = 'Other' THEN Percentage_Difference END) AS Other
FROM table t
GROUP BY Report_Gap, Report_Group;

如果Report_Group有一个或多个Percentage_Difference，那么您可能需要SUM()而不是MAX().

推荐阅读