Define column name dynamically by current date in SQL Server

Question

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

You cannot do this in T-SQL - your best bet would bet to return the values as you have them in your query now, and additionally return the spelled out month names for each of those six months. Let the frontend (web app or whatever) handle the actual display using the month names instead of the column names — marc_s
– marc_s, Commented Mar 25, 2012 at 10:31

Community · Accepted Answer · 2017-05-23 11:50:32Z

2

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.

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Mar 25, 2012 at 10:46

Christian Specht

36.6k16 gold badges132 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tab Alleman Over a year ago

@betite why haven't you selected this as the answer?

Collectives™ on Stack Overflow

Define column name dynamically by current date in SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related