How to use month names to replace headers in a select on SQL Server-CodePudding

I have a table with these columns:

Id, policy_num, month1, month2, currentMonth.

The data is updated through an SP, and in order to show the correct months in the report I have to modify the headers by changing them to the specific name of the month, example: change "currentMonth" to "January", "month2" to "December", "month3" to "November".

I tried the following:

declare @currentMonth varchar(20) = DATENAME(month, GETDATE());
declare @month2 varchar(20) = DATENAME (month, dateAdd(month, -1, GETDATE()));
declare @month3 varchar(20) = DATENAME (month, dateAdd(month, -2, GETDATE()));

select id, policy_num, month1 as [@currentMonth], month2 as [@month2], month3 as [@month3] from fact_report;`

But it doesn't work, if I print the value of each variable it shows correctly the months, but it is not showing the data on the header when I run the select query.

What can I do? Is there some kind of cast missing?

Thanks for your help.

CodePudding user response：

Unfortunately, you'll need to either use dynamic SQL or set the columns names via calling code or reporting tool:

declare @currentMonth varchar(20) = DATENAME(month, GETDATE());
declare @month2 varchar(20) = DATENAME (month, dateAdd(month, -1, GETDATE()));
declare @month3 varchar(20) = DATENAME (month, dateAdd(month, -2, GETDATE()));

declare @sql varchar(150) = 'select id, policy_num, month1 as ['   @currentMonth   '], month2 as ['   @month2   '], month3 as ['   @month3   '] from fact_report;';

exec(@sql)

The good news is this is fairly safe, as dynamic SQL goes. To have an injection issue some must have already had admin access to your server to change the system's month names.