Concatenating Aliases in column names

Transact-SQL
1

Declare @StartYear as datetime,
@EndYear as datetime

Is there a way to concatenate the alias in my case statement to make it be dynamic? Instead of hard typing 'Status as of 2014' I want to build that each time. Short of a dynamic sql string I can't find a way to do it. Thanks

Set @StartYear = '01/01/2014'
Set @EndYear = '12/31/2014'

Select
case when YEAR(r.TerminationDate) > year(@StartYear) then 'Active in ' + cast(year(@StartYear) AS varchar (20))
when YEAR(r.TerminationDate) = year(@StartYear) then 'Termed in ' + cast(year(@StartYear) AS varchar (20))
else '' end as 'Status as of 2014',

2

You are correct. T-SQL does not provide for a way to alias columns in the select list dynamically, unless you want to use dynamic SQL. Would be nice to alias using a variable, wouldn't it? Unfortunately, there is no provision for that.

1 Like