sql server - Naming a Column using Variable

Question

I wish to calculate Ascending Points Diff for all players in a sweep over a series of games. I wish to have the first column name PD1 with the least Points Difference, PD2 next lowest and so on. I wish to do this using a variable which just adds the integer to the end of 'PD' based on the number of games. I keep getting an error when I add AS @ColumnName to the end of the Calculation.

USE [Rugby Pools]


DECLARE @counter int
DECLARE @MaxPlayer int
DECLARE @ColumnName varchar(50)

SET @counter = (SELECT MIN([Player_ID]) FROM [dbo].[Players])
SET @MaxPlayer = (SELECT MAX([Player_ID]) FROM [dbo].[Players])


DECLARE @gamecounter int
DECLARE @MaxGame int
SET @gamecounter = (SELECT MIN([Game_ID]) FROM [dbo].[Match])
SET @MaxGame = (SELECT MAX([Game_ID]) FROM [dbo].[Match])
SET @ColumnName='PD'+@gamecounter

WHILE @gamecounter <= @MaxGame

BEGIN


WHILE @counter <= @MaxPlayer

BEGIN



SELECT     TOP (@gamecounter)   dbo.Players.Player_ID, dbo.Entries.Game_ID, ABS(ABS(dbo.Entries.Home_Score-dbo.Entries.Away_Score)-(dbo.Match.Home_Score-dbo.Match.Away_Score)) AS @ColumnName
FROM            Entries INNER JOIN
                         Match ON Entries.Game_ID = Match.Game_ID INNER JOIN
                         Players ON Entries.Player_ID = Players.Player_ID

                         WHERE dbo.Match.Home_Score IS NOT NULL AND dbo.Players.Player_ID=@counter

                         ORDER BY Players.Player_ID, PointsDiff1 ASC

SET @Counter += 1

                         END                        

SET @gamecounter += 1

END 

Once working, I will be able to alter it to update a table which will allow me to provide a league table of players decided by wins, followed by best points difference over the course of the tournament.

Answer 1

Change your final query section to dynamic sql like this. It can be achieved only by dynamic sql.

 declare @query nvarchar(max)
 set @query = ' SELECT TOP (' + cast(@gamecounter as varchar(10)) + ') dbo.Players.Player_ID, 
               dbo.Entries.Game_ID, ABS(ABS(dbo.Entries.Home_Score-dbo.Entries.Away_Score)-(dbo.Match.Home_Score-dbo.Match.Away_Score)) AS ' + QUOTENAME(@ColumnName) + '
    FROM            Entries INNER JOIN
                             Match ON Entries.Game_ID = Match.Game_ID INNER JOIN
                             Players ON Entries.Player_ID = Players.Player_ID

                             WHERE dbo.Match.Home_Score IS NOT NULL AND dbo.Players.Player_ID= ' + cast(@counter as varchar(10)) + '
                             ORDER BY Players.Player_ID, PointsDiff1 ASC'

  sp_executesql @query