Define column name dynamically by current date in

问题:

I have this SELECT statement:

SELECT  
    ProjectId, 
    ProjectName,               
    D3 as "three month ago",
    D2 as "two month ago",
    D1 as "last month",
    F0 as "current month",
    F1 as "next month",
    F2 as "next two month",        
FROM
    View_Year_Forcast

I want to specify the column name according to the current month.

I have a function get_month_name that gets the month symbol like (d1,d2,d3,f0..) and return a string that represent the month name in Hebrew.

What is the best way to solve the problem?

Just for the record I am trying to do this in a stored procedure in SQL Server

回答1:

It is possible to do this with dynamic SQL:

declare @month nvarchar(50)
declare @sql nvarchar(1000)

set @month = 'March' -- execute your `get_month_name` function here instead

set @sql = 'select D3 as ' + @month + ' from View_Year_Forcast'

exec sp_executesql @sql

However, dynamic SQL has its issues. See The Curse and Blessings of Dynamic SQL for an in-depth explanation.

So if it's somehow possible, I would rather not use dynamic SQL, but do something like marc_s suggested in his comment:
leave the query as it is, return the month names in addition and let the client display the month names in the right place.