Dynamically name a column based on a WHERE condition

Question

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())

https://stackoverflow.com/questions/34411699/generate-column-name-dynamically-in-sql-server — Dale K, Feb 18 '19 at 23:04
Possible duplicate of [Generate column name dynamically in sql server](https://stackoverflow.com/questions/34411699/generate-column-name-dynamically-in-sql-server) — Dale K, Feb 19 '19 at 00:19

score 1 · Accepted Answer · answered Feb 18 '19 at 23:07

1

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)

answered Feb 18 '19 at 23:07

John Cappelletti

79,615
7
44
66

So to run my query, it needs to be in a DECLARE statement? – Roger Steinberg Feb 18 '19 at 23:18
if you want the column name to change on the fly, then yes. – Greg Feb 19 '19 at 00:14

Dynamically name a column based on a WHERE condition

1 Answers1