Convert Rows to column rows in SQL Server -

i looking way convert rows in table columns rows , assign column names each.

players id  team john    1   t1 carmel  1   t1 jack    1   t1 james   1   t1 

changed to:

id  team  p1    p2      p3      p4 1   t1    john  carmel  jack    james 

there can number of players in above example. tried using pivot. change rows columns not in fashion looking for.

here 1 way using dynamic crosstab. read article jeff moden more details.

declare @sql1 varchar(4000) = '' declare @sql2 varchar(4000) = '' declare @sql3 varchar(4000) = ''  select @sql1 = 'select      id     , team '  select @sql2 = @sql2 + '   , max(case when rn = ' + convert(varchar(10), rn) + ' players end) ['  + convert(varchar(10), rn) + ']' + char(10) from(     select distinct         rn = row_number() over(partition id, team order (select null))     tbl )t order rn  select @sql3 = 'from(     select *,         rn = row_number() over(partition id, team order (select null))     tbl )t group id, team'  print (@sql1 + @sql2 + @sql3) exec (@sql1 + @sql2 + @sql3) 

sql fiddle