column alias as variable

Is there a way to select a column as an alias using a variable for the alias? something like this:

SELECT Column1 as @.myVariable FROM Table1

Not directly in a SELECT statement. You can use dynamic SQL like:

set @.sql = N'SELECT Column1 as ' + quotename(@.myVariable) + N' FROM Table1'

exec sp_executesql @.sql

But it is not going to be pretty if you want to do this for multiple columns and dynamic SQL has security implications/management issues. Why do you want to do this? How will the client handle this if the column names can be modified arbitrarily? One way to do this is to fix the column names as c1, c2, c3 etc and have a separate result set or metadata that contains the user friendly names for c1, c2, c3 respectively.

|||

The user enters a period length such as 30 days and the report displays the period date ranges as the coulmn name of a pivot.

like this:

Location 1-30 31-60 61-90

Texas 10 3 2

Florida 5 8 7