如何在SQL中汇总多个学生的视频游戏分数

Question

我有 3 列数据。 第 1 列是学生的姓名，第 2 列是他们玩过的游戏的名称。 他们可以玩 10 种可能的游戏，简单地命名为游戏 1 到 游戏 10'。 第 3 列包含学生在游戏中获得的分数，可能是正数或负数。 学生可以玩 10 场比赛中的任何一种 0 次到多次(无限制(。 每次学生玩任何游戏时，都会创建一行数据。

我想总结学生在 10 场比赛中的任何一场中获得的所有分数，按学生排序。 因此，行标题将是学生姓名，列标题将是游戏的名称(游戏 1 到游戏 10(。

这是我一直在做的代码：

SELECT [STUDENT],
SUM ( [SCORE]) 
FROM [Students].[dbo].GameScores
WHERE [GAME] = 'GAME 1'  --swap in the game name that you want
GROUP BY [STUDENT]

我在 10 场比赛中的每一场比赛都运行了 10 次上述代码，将游戏 1 换成游戏 2，然后是游戏 3，依此类推。 然后，我会根据需要手动将结果剪切并粘贴到Excel中。 随着游戏数量越来越大(到 100(，我显然不想运行上面的代码来手动提取所有 100 场比赛的分数总和。

如何更改上述查询，以便我可以迭代所有 10 个游戏(或任何 X 个游戏，也许 X 等于 100(并显示结果，以便行标题是学生姓名，列标题是游戏名称，单元格具有学生在特定游戏中的分数总和(可以是负数或正数(。

我正在使用 SQL Server 2008 R2。

Answer 1

这个怎么样：

SELECT student, [Game 1], [Game 2], [Game 3]
FROM Students.dbo.GameScores g
PIVOT (SUM(Score) FOR Game IN ([Game 1], [Game 2], [Game 3])) pvt

您可以将游戏 4 添加到示例中 10 以满足您的要求。

Answer 2

create table #temp (student varchar(24), score int, Gametitle varchar(20))
    insert into #temp (student , score , Gametitle )
    values
    ('Student1', 100, 'Game1')
    ,('Student1', 90, 'Game2')
    ,('Student1', 80, 'Game3')
    ,('Student2', 100, 'Game1')
    ,('Student2', 100, 'Game2')
    ,('Student2', 100, 'Game3')
    ,('Student3', 100, 'Game1')
    ,('Student3', 50, 'Game4')
    ; 
select 
    student 
    ,[Game1], [Game2], [Game3],[Game4]


from 
#temp

pivot (
    sum(score)
        for Gametitle in ([Game1], [Game2], [Game3],[Game4])) as pivottable
        drop table #temp

结果看起来像

student |   Game1|  Game2|  Game3|  Game4|
Student1|   100  |    90 |    80 |   NULL|
Student2|   100  |   100 |    100|   NULL|
Student3|   100  |   NULL|   NULL|   50  |

如果由于某种原因您不想输入所有游戏标题，请尝试此操作

create table #temp (student varchar(24), score int, Gametitle varchar(20), league varchar(20))
insert into #temp (student , score , Gametitle,league )
values
('Student1', 100, 'Game1','silver')
,('Student1', 90, 'Game2','silver')
,('Student1', 80, 'Game3','silver')
,('Student2', 100, 'Game1','silver')
,('Student2', 100, 'Game2','silver')
,('Student2', 100, 'Game3','silver')
,('Student3', 100, 'Game1','silver')
,('Student3', 50, 'Game4','silver')
,('Student1', 50, 'Game1','silver')

; 
declare @columns nvarchar(max)
select @columns = Coalesce(@columns + ',['+ [Gametitle]+']', '[' + [Gametitle] +']') 
                from (select distinct Gametitle from #temp) columns
                order by Gametitle
declare @query nvarchar (max)
set @query ='
select 
     *

from 
#temp

pivot (
    sum(score)
        for Gametitle in (' + @columns + '))pv

        '
        exec sp_executesql @query
        drop table #temp

Answer 3

您想要

的是将垂直表格转换为水平表格。对此的解决方案是显式或隐式定义的 PIVOT。

如果您希望查询是完全动态的(不是向查询添加新游戏，而是查询将自行获取(，您将需要使用动态 SQL，它将分析您的数据并准备包含所有字段的透视查询，如下所示：

/* parameters' definition and initializing */
declare @sql nvarchar(max), @columns nvarchar(max);
set @columns = '';
/* Get the unique list of played games */
select @columns = columns + quotename(Game) + ','
from
(
    select distinct Game
    from Students.dbo.Games        
) x
order by Game; /* this will place the payed games in the alphabetical order */
set @columns = left(@columns, len(@columns) - 1); /* to remove last coma */
/* Preparing dynamic SQL based on the played games */
set @sql = 'SELECT student, ' + @columns'
FROM Students.dbo.GameScores g
PIVOT (SUM(Score) FOR Game IN (' + @columns + ')) pvt';
/* Executing the dynamic SQL */
exec sp_executesql @sql;