sql-server-2005 - 在 SQL Server 2005 中透视表以多次包含相同的列

Question

我需要旋转名为 tblGameRoleName 的下表 -

游戏角色名称
Volley 教练苏加塔
Volley 运动员拉金德兰
Volley 运动员朱诺
Volley 运动员英迪拉
Volley 运动员加内什
Volley 运动员瓦桑斯
网球教练拉杰什库马尔
网球运动员维维克
网球运动员鲁巴拉

到下表多次显示“玩家”列 -

游戏教练 Player1 Player2 Player3 Player4 Player5
Volley Sujatha Rajendran Juno Indira Ganesh Vasanth
网球 Rajeshkumar Vivek Rubala NULL NULL NULL

问题在于，不同“游戏”的“玩家”数量可能会增加，结果表应显示所有游戏的所有玩家。例如 - 如果我将以下“板球”团队添加到此表中 -

板球教练加里
板球运动员病毒
板球运动员高蒂
板球运动员萨钦
板球运动员马希
板球运动员尤维
板球运动员苏雷什
板球运动员维拉特
板球运动员巴吉
板球运动员扎希尔
板球运动员伊尚特
板球运动员阿什

然后结果表应显示 11 个玩家列。

这可以借助 PIVOT 功能来实现吗？如果没有，请提出正确的方法来实现结果表。

Answer 1

这在前端报告/显示应用程序中可能更容易，但对于 sql，您需要执行动态数据透视。但是由于列是由连续的玩家编号命名的，并且特定的玩家因游戏而异，因此您不能使用典型的动态 sql 示例。

这是一种方法:

样本数据

set ansi_warnings off
set nocount on
create table #t (Game varchar(20), Role varchar(15), [Name] varchar(20))
insert #t
          select 'VolleyBall', 'Coach', 'Sujatha'
union all select 'VolleyBall', 'Player', 'Rajendran'
union all select 'VolleyBall', 'Player', 'Juno'
union all select 'VolleyBall', 'Player', 'Indira'
union all select 'VolleyBall', 'Player', 'Ganesh'
union all select 'VolleyBall', 'Player', 'Vasanth'
union all select 'Tennis', 'Coach', 'Rajeshkumar'
union all select 'Tennis', 'Player', 'Vivek'
union all select 'Tennis', 'Player', 'Rubala'
union all select 'Cricket', 'Coach', 'Gary'
union all select 'Cricket', 'Player', 'Viru'
union all select 'Cricket', 'Player', 'Gauti'
union all select 'Cricket', 'Player', 'Sachin'
union all select 'Cricket', 'Player', 'Mahi'
union all select 'Cricket', 'Player', 'Yuvi'
union all select 'Cricket', 'Player', 'Suresh'
union all select 'Cricket', 'Player', 'Virat'
union all select 'Cricket', 'Player', 'Bhajji'
union all select 'Cricket', 'Player', 'Zaheer'
union all select 'Cricket', 'Player', 'Ishant'
union all select 'Cricket', 'Player', 'Ashish'

创建动态 SELECT 和 PIVOT 子句以及 EXEC'd 语句

declare @max int
select top 1 @max = count(*)
from #t 
where role = 'player' 
group by game 
order by count(*) desc

declare @sel varchar(2000)
       ,@piv varchar(2000)

;with nos (n) as (select 1 union all select n+1 from nos where n < @max)
select @sel = coalesce(@sel + ', ' 
       + 'max([' + convert(varchar(2), n) + ']) as player' + convert(varchar(2), n)
       , 'max([' + convert(varchar(2), n) + ']) as player' + convert(varchar(2), n)
       )

       ,@piv = coalesce(@piv + ',[' + convert(varchar(2), n) + ']', '[' + convert(varchar(2), n) + ']')
from   nos
-----------------------------------------------------------------------------

exec('
select p.game
       ,max(p.coach) as coach
       ,' + @sel + '
from   (
       select game
              ,case when role = ''coach'' then [name] end as coach
              ,case when role = ''player'' then [name] end as player
              ,row_number() over (partition by game, role order by name) as seq
       from   #t
       ) d
pivot (max(player) for seq in (' + @piv + ')) p
group by p.game
')

go
drop table #t

输出:

game                 coach                player1              player2              player3              player4              player5              player6              player7              player8              player9              player10             player11
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
Cricket              Gary                 Ashish               Bhajji               Gauti                Ishant               Mahi                 Sachin               Suresh               Virat                Viru                 Yuvi                 Zaheer
Tennis               Rajeshkumar          Rubala               Vivek                NULL                 NULL                 NULL                 NULL                 NULL                 NULL                 NULL                 NULL                 NULL
VolleyBall           Sujatha              Ganesh               Indira               Juno                 Rajendran            Vasanth              NULL                 NULL                 NULL                 NULL                 NULL                 NULL