CODEWITHSUNDEEP
sqlsql-server
riann
riann

Reputation: 11

Select value and put as/alias variable name?

Based on the example below, I need to custom header column name based on the variable value. Is there a way to do this? Thank you.

DECLARE @date DATE
SET @date = '2020-10-2'

DECLARE @monthperYear DECIMAL
SET @monthperYear =  (SELECT ((cast (month(@date) as INT) / 12.0) * 100))

DECLARE @yearBefore INT
SET @yearBefore = (SELECT (year(@date) -1))

DECLARE @percentageheadername VARCHAR (30)
SET @percentageheadername =(SELECT CAST (@monthperYear as varchar(5))+ '% Full Year ' + CAST (@yearBefore as CHAR (5)) +'Actual' as percentageHeadername)



Create Table #sample
(
name varchar (50)
, percentage DECIMAL (10,2)
)

INSERT INTO #sample
(name, percentage)
VALUES
('Marcus', '8'), ('John', '70')

select name, **percentage as @percentageheadername** from #sample
DROP TABLE #sample

Upvotes: 0

Views: 1210

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

You can use dynamic sql to execute your final query.

Query:

  DECLARE @date DATE
     SET @date = '2020-10-2'
     
     DECLARE @monthperYear DECIMAL
     SET @monthperYear =  (SELECT ((cast (month(@date) as INT) / 12.0) * 100))
     
     DECLARE @yearBefore INT
     SET @yearBefore = (SELECT (year(@date) -1))
     
     DECLARE @percentageheadername VARCHAR (30)
     SET @percentageheadername =(SELECT CAST (@monthperYear as varchar(5))+ '% Full Year ' + CAST (@yearBefore as CHAR (5)) +'Actual' as percentageHeadername)
                      
     Create Table #sample
     (
     name varchar (50)
     , percentage DECIMAL (10,2)
     )
     
     INSERT INTO #sample
     (name, percentage)
     VALUES
     ('Marcus', '8'), ('John', '70')
     
     declare @sql varchar(500)
                         
     SET @SQL = 'select name, percentage  ['+@percentageheadername +'] from #sample'
      
     EXEC (@SQL)
     
     DROP TABLE #sample

Output:

name 83% Full Year 2019 Actual
Marcus 8.00
John 70.00

db<fiddle here

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30665

you can use dynamic sql

set @percentageheadername = 'SomeName'
set @sqlCommand = 'select name, percentage as ' + @percentageheadername + ' from #sample'

EXEC (@sqlCommand)

Upvotes: 0

Related Questions