我想知道是否可以使用计算函数作为列标头。请参阅下面我的代码。我想用昨天的日期作为和大小写列标题。
DECLARE @A AS VARCHAR(12)
SET @A = GETDATE()-1
select Data,
sum(case when DATEDIFF(DAY, Dt, GETDATE()) = 1and Type = 'Stock'
then Spend end) AS @A
from dbo.vw_ZZ_AS_ComplianceLeagueTable
group by Data
我强烈建议在应用程序层中进行这种重命名。
但是如果你仍然坚持你可以使用Dynamic-SQL:
LiveDemo
DECLARE @A AS VARCHAR(12)= GETDATE()-1;
DECLARE @sql NVARCHAR(MAX) =
N'SELECT [Data],
SUM(CASE WHEN DATEDIFF(DAY, Dt, GETDATE()) = 1 AND Type = ''Stock'' THEN SPEND END) AS <placeholder>
FROM #vw_ZZ_AS_ComplianceLeagueTable
GROUP BY [Data];';
SET @sql = REPLACE(@sql, '<placeholder>', QUOTENAME(@A));
EXEC [dbo].[sp_executesql]
@sql;
另一个可能性是使用sp_rename
函数:
LiveDemo2
DECLARE @A AS VARCHAR(12)= GETDATE()-1;
SELECT [Data],
SUM(CASE WHEN DATEDIFF(DAY, Dt, GETDATE()) = 1 AND Type = 'Stock' THEN SPEND END) AS [placeholder]
INTO #temp
FROM #vw_ZZ_AS_ComplianceLeagueTable
GROUP BY [Data];
DECLARE @new_name NVARCHAR(128) = QUOTENAME(@A);
EXEC tempdb..sp_rename '#temp.placeholder', @new_name, 'COLUMN';
SELECT *
FROM #temp;