带计数的 SQL Server 数据透视子句

Question

我正在使用 Microsoft SQL Server 并尝试在下面编写以下查询。我想使用 Pivot 子句来计算美国和俄罗斯在 2000 年奥运会上的金牌数。但是我的输出对于这两个国家都是零。我知道我可以使用 group by 来获得所需的结果(请参见下面的打印屏幕)。但是如何使用 pivot 子句做到这一点呢？

请查看数据集的打印屏幕和下面的输出

select 
    'Gold' as total_m, 
    ['USA'] as USA, ['RUS'] as RUS
from 
    (select 
         country, medal, year
     from 
         summer
     where 
         medal = 'Gold'
         and year = 2000 
         and country in ('USA', 'RUS')) as SourceTable
pivot
    (count(medal)   
     for country in (['USA'],['RUS'])) as PivotTable;

数据集

输出

分组依据

Answer 1

从 pivot 列列表中删除 引号，

select 'Gold' as total_m, [USA] as USA, [RUS] as RUS
from 
    (select country, medal, year
     from summer
     where medal = 'Gold'
       and year = 2000 
       and country in ('USA', 'RUS')) as SourceTable
pivot
    (count(medal)   
     for country in ([USA],[RUS])) as PivotTable;