将不同的行属性转置/旋转为列并将另一个属性分组?

2022-01-22 00:00:00 pivot sql sql-server

可能重复:
SQL Server 动态 PIVOT 查询?

是否可以对下表执行查询:

Is it possible to execute a query on the following table:

Game    Player  Goals
-----   ------  ------
Game1   John    1
Game1   Paul    0
Game1   Mark    2
Game1   Luke    1
Game2   John    3
Game2   Paul    1   
Game2   Luke    1
Game3   John    0
Game3   Mark    2

结果如下:

Game    John    Paul    Mark    Luke
-----   ----    ----    ----    ----
Game1   1       0       2       1
Game2   3       1       -       1
Game3   0       -       2       -

它将每个不同的玩家变成一列，并将游戏分组，为每位玩家提供每场比赛的目标.

It turns each distinct player into a column and groups the games gives goals per game per player.

推荐答案

可以使用PIVOT函数.如果您有已知数量的列，那么您可以对这些值进行硬编码:

You can use the PIVOT function. If you have a known number of columns, then you can hard-code the values:

select *
from
(
  select game, player, goals
  from yourtable
) src
pivot
(
  sum(goals)
  for player in ([John], [Paul], [Mark], [Luke])
) piv
order by game

参见 SQL Fiddle with Demo

如果你有未知数量的列，那么你可以使用动态sql:

If you have an unknown number of columns, then you can use dynamic sql:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(player) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT game, ' + @cols + ' from 
             (
                select game, player, goals
                from yourtable
            ) x
            pivot 
            (
                sum(goals)
                for player in (' + @cols + ')
            ) p '

execute(@query)

参见 SQL Fiddle with Demo

相关文章