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.