CODEWITHSUNDEEP
sql-servert-sqlcolumnheader
Krishn
Krishn

Reputation: 873

Use function in Sql column header

I wanted to know if it's possible to use a calculated function as a column header. Please see my code below. I would like to use yesterday's date for the sum case column header.

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

Upvotes: 0

Views: 1965

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

I strongly recommend to do this kind of renaming in application layer.

But if you still insist you can use 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;

Another possibility is to use sp_rename function:

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;

Upvotes: 2

Related Questions