Dynamically name a column based on a WHERE condition

Objective:

I am running a query on a weekly basis and would like one of my column to return the ISO_WEEK number with the prefix ‘W’. For example: W1.

More specifically, I would like to reproduce the following condition into a column name.

DATEPART(ISO_WEEK, table.login) = DATEPART(ISO_WEEK,GETDATE())-2

Main Query is:

 SELECT 
     CAST(t.log_time as Date)           AS Week1
    ,DATEPART(ISO_WEEK,t.log_time)      AS Week_Number -- Dynamically named with ISO_WEEK
    ,email                              AS Emails
 FROM table.memberlog as t
 WHERE 1=1
    AND DATEPART(ISO_WEEK, t.log_time) = DATEPART(ISO_WEEK,GETDATE())-2
    AND DATEPART(YEAR, t.log_time) = DATEPART(YEAR,GETDATE())

Answer

SQL Server is declarative by design and does not support macro substitution. This leaves you with Dynamic SQL such as …

Declare @SQL varchar(max) = '
SELECT 
     CAST(t.log_time as Date)           AS Week1
    ,DATEPART(ISO_WEEK,t.log_time)      AS '+concat('W',DATEPART(ISO_WEEK,GETDATE())-2)+'
    ,email                              AS Emails
 FROM table.memberlog as t
 WHERE 1=1
    AND DATEPART(ISO_WEEK, t.log_time) = DATEPART(ISO_WEEK,GETDATE())-2
    AND DATEPART(YEAR, t.log_time) = DATEPART(YEAR,GETDATE())
'

Exec(@SQL)