在SQL Server中按当前日期动态定义列名 [英] Define column name dynamically by current date in SQL Server

问题描述

我有这个SELECT声明:

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.

我有一个函数get_month_name，该函数获取类似于(d1，d2，d3，f0 ..)的月份符号，并返回一个表示希伯来语中月份名称的字符串.

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?

仅出于记录目的，我试图在SQL Server的存储过程中做到这一点

推荐答案

使用动态SQL可以做到 :

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

但是，动态SQL存在其问题.有关详细说明，请参见动态SQL的诅咒和祝福.

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

因此，如果有可能，我宁愿不使用动态SQL，但要执行

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.

这篇关于在SQL Server中按当前日期动态定义列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！