Pivoting a table in SQL Server 2005 to contain same column multiple times
I need to pivot the following table named tblGameRoleName -
Game Role Name VolleyBall Coach Sujatha VolleyBall Player Rajendran VolleyBall Player Juno VolleyBall Player Indira VolleyBall Player Ganesh VolleyBall Player Vasanth Tennis Coach Rajeshkumar Tennis Player Vivek Tennis Player Rubala
to the following table which has the 'Player' column multiple times -
Game Coach Player1 Player2 Player3 Player4 Player5 VolleyBall Sujatha Rajendran Juno Indira Ganesh Vasanth Tennis Rajeshkumar Vivek Rubala NULL NULL NULL
The problem is that the number of 'p开发者_Go百科layers' can increase for different 'games' and the result table should show all the players for all the games. For example - If I add the following 'cricket' team to this table -
Cricket Coach Gary Cricket Player Viru Cricket Player Gauti Cricket Player Sachin Cricket Player Mahi Cricket Player Yuvi Cricket Player Suresh Cricket Player Virat Cricket Player Bhajji Cricket Player Zaheer Cricket Player Ishant Cricket Player Ashish
Then the result table should show 11 player columns.
Can this be achieved with the help of the PIVOT function? If not, please suggest the correct way to achieve the result table.
This may be easier in a front end reporting/display application but for sql you need to perform a dynamic pivot. But since the columns are aliased by a sequential player number and the specific players vary by game you can't use the typcial dynamic sql examples.
Here is one way to do it:
sample data
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'
create dynamic SELECT and PIVOT clauses and the EXEC'd statement
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
OUTPUT:
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
精彩评论