Changeing column name to date in SQL server

See more:

SQL

Dear Madam/Sir,
Good day,

I have table with 11 column and I want to change column name of 10 of them to yesterday date - before yesterday date ..... etc

the table structure is :


-------------------------------------------------------------------------------------
name | column 1 | | column 2 | | column 3 | | column 4 | | column 5 | | column 6 |
-------------------------------------------------------------------------------------
var1 | 159 | | 200 | | 300 | | 179 | | 410 | | 999 |


P.S the table structure after excute query below :

SELECT DISTINCT 
   case Name when '123' then 'var1'   when   else name end as name,
        sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -1, GETDATE()) as date) THEN cast (ta.VALUE as float) ELSE 0 END) as getdate ,   
        sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -2, GETDATE()) as date) THEN cast (ta.VALUE as float) ELSE 0 END),
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -3, GETDATE()) as date) THEN cast (ta.VALUE as float) ELSE 0 END)   ,
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -4, GETDATE()) as date) THEN cast (Ta.VALUE as float) ELSE 0 END) ,
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -5, GETDATE()) as date) THEN cast (Ta.VALUE as float) ELSE 0 END)  ,
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -6, GETDATE()) as date) THEN cast (Ta.VALUE as float) ELSE 0 END)   ,
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -7, GETDATE()) as date) THEN cast (Ta.VALUE as float) ELSE 0 END)   ,
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -8, GETDATE()) as date) THEN cast (Ta.VALUE as float) ELSE 0 END)   ,
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -9, GETDATE()) as date) THEN cast (Ta.VALUE as float) ELSE 0 END)   ,
		sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -10, GETDATE()) as date) THEN cast (Ta.VALUE as float) ELSE 0 END)   

        FROM tabel name 
GROUP BY  name

What I have tried:

the result now
-------------------------------------------------------------------------------------
name | column 1 | | column 2 | | column 3 | | column 4 | | column 5 | | column 6 |
-------------------------------------------------------------------------------------
var1 | 159 | | 200 | | 300 | | 179 | | 410 | | 999 |


and what we expect is :
------------------------------------------------------------------------------------------------------
name | 20/6/2017 | | 19/6/2017 | | 18/6/2017 | | 17/6/2017 | | 16/6/2017 | | 15/6/2017 |
------------------------------------------------------------------------------------------------------
var1 | 159 | | 200 | | 300 | | 179 | | 410 | | 999 |



I tried to create varible

declare @1  date = DATEADD(dd, -1, GETDATE())

and make the result of this varible as a column name

sum(CASE WHEN  cast (dbo.fa_ConvertToDateTime(TIMESTAMP_S) as date) =  cast (DATEADD(dd, -2, GETDATE()) as date) THEN cast (ta.VALUE as float) ELSE 0 END) as (Select @1),

I belive its wrong and I couldn't find the way to do it.

thanks in advanse.

have a good day,