If you want it to be truly dynamic and decide on the column's name during runtime, you can do it with dynamic code. Notice that dynamic code has its price especially regarding to security. If you'll explain a little as for why you need this, maybe some of us will come with a better approach. In any case Bellow is a way to do it with dynamic SQL

create table #temp(name varchar(10), sale int)

insert into #temp values('john',1000)

insert into #temp values('Mike',500)

insert into #temp values('Abhas',200)

declare @sql varchar(200)

select @sql = concat('select name, sale as sale',year(getdate()),' from #temp')

exec (@sql)

drop table #temp

Adi

Edit:

Only after posting the answer I've noticed that it was asked for SQL Server 2008. This means that you can't use the concat function. You can still use the same way, but instead of working with concat function, you'll have to use the plus sign (+) and cast the results of year function as varchar.

Adi