Using a declared variable in the creation of an alias

Question

DECLARE @PERIOD INT

SELECT @PERIOD = 1

SELECT @NUMERATOR / @DENOMINATOR * 100  AS 'Period 1'

Is it possible to instead of using AS 'Period 1' to instead use @PERIOD to replace the 1 in AS 'Period 1'

I was thinking something along the lines of AS 'Period' + @PERIOD From the comments It appears this is not something that can not be done without dynamicSQL.

I really appreciate the help.

Answer 1

A solution without dynamic sql may be like this:

DECLARE @PERIOD INT

SELECT @PERIOD = 1

declare @PeriodX as varchar(99) = 'Period ' +  cast(@PERIOD as varchar(9))
create table #t (c1 int)
exec tempdb..sp_rename '#t.c1', @PeriodX

insert #t
SELECT @NUMERATOR / @DENOMINATOR * 100

select * from #t
drop table #t

Answer 2

DECLARE @PERIOD INT = 1

DECLARE @tmpExecVar AS NVARCHAR(max) =N'SELECT 1 AS [' + 'Period ' + Cast(@PERIOD AS VARCHAR) + ']'

EXEC(@tmpExecVar)