mdreed
asked on
Can SQL column alias be an expression?
I have a select statement that includes:
SUM(CASE WHEN E.DateAdded > GetDate() - 7 THEN 1 ELSE 0 END)
AS LastWeek
But what I would prever is to return something like:
AS 'Since ' + GetDate() - 7
Is there some way to use an expression as the alias?
Also, is there a way to force a column heading to two lines like:
'Since'
'Last Week'
SUM(CASE WHEN E.DateAdded > GetDate() - 7 THEN 1 ELSE 0 END)
AS LastWeek
But what I would prever is to return something like:
AS 'Since ' + GetDate() - 7
Is there some way to use an expression as the alias?
Also, is there a way to force a column heading to two lines like:
'Since'
'Last Week'
you can't use an expression for an alias
Hello,
This can be achieved by use of dynamic SQL. See below:
Harish
This can be achieved by use of dynamic SQL. See below:
declare @alias varchar(100),
@sql varchar(1000)
--Example: Dynamic heading
set @alias = 'Since ' + convert (varchar, GetDate() - 7, 101)
set @sql = 'Select GetDate() as [' + @alias + ']'
exec (@sql)
--Example: Multi-line Dynamic heading
set @alias = 'Since ' + char(13) + char(10) + convert (varchar, GetDate() - 7, 101)
set @sql = 'Select GetDate() as [' + @alias + ']'
exec (@sql)
Thanks,Harish
ASKER
Harish
Works great as a script, thanks. Is there any way to use the code in an SQL View?
Works great as a script, thanks. Is there any way to use the code in an SQL View?
you can't use it in a view
you can try to create a user function with that code,
and then create a view that queries the user function
and then create a view that queries the user function
Since column names are created when viiew is created, it will not be possible to use a view instead of dynamic sql.
If you can explain your scenario in detail, we can think about possible solutions. Another approach can be to create a table dynamically with required dynamic column names (again using dynamic sql), populate this table with your current SELECT (from table/view), and then do a SELECT from this dynamically created table.
Thanks,
Harish
If you can explain your scenario in detail, we can think about possible solutions. Another approach can be to create a table dynamically with required dynamic column names (again using dynamic sql), populate this table with your current SELECT (from table/view), and then do a SELECT from this dynamically created table.
Thanks,
Harish
ASKER
I just have to provide some counts, total and total for the past 7 days, on an as-needed basis. The easiest solution was to provide an Excel query that retrieves the data from a view I created, as opposed to writing a full app unnecessarily. Presently, the view returns a column heading of 'Last Week', as i mentioned, I would prefer to have it read the equivilent of 'Since' + GetDate() - 7.
Now, if there were some way the Excel query could launch a script or stored procedure (that could create the table as you noted), then that might work.
Now, if there were some way the Excel query could launch a script or stored procedure (that could create the table as you noted), then that might work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Harish
Looks like we have a winner. Either solution looks pretty viable. Thanks for your help.
Looks like we have a winner. Either solution looks pretty viable. Thanks for your help.