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'
mdreedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
you can't use an expression for an alias
Harish VargheseProject LeaderCommented:
Hello,

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)

Open in new window

Thanks,
Harish
mdreedAuthor Commented:
Harish

Works great as a script, thanks.  Is there any way to use the code in an SQL View?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

momi_sabagCommented:
you can't use it in a view
momi_sabagCommented:
you can try to create a user function with that code,
and then create a  view that queries the user function
Harish VargheseProject LeaderCommented:
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
mdreedAuthor Commented:
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.
Harish VargheseProject LeaderCommented:
Hello,

I can think of 2 approaches if you want data to be retrieved into Excel:

1. Write a stored proc that will execute the dynamic SQL (no need to create separate table with dynamic headers), and then execute this proc from Excel.
2. Compose the dynamic sql within Excel (VBA code) and then execute the query from Excel itself. Below is a sample VBA Code that does the same. REMEBER to change servername and password:
Option Explicit

Sub RetrieveData()

    Dim conn As ADODB.Connection
    Dim SQLQuery As String
    
    Set conn = New ADODB.Connection
    ' Create a connection object.
    Set conn = New ADODB.Connection
    
    'Set connection properties
    conn.Provider = "SQLOLEDB;"
    conn.Properties("Data Source").Value = "YourServerName"
    conn.Properties("User id") = "sa"
    conn.Properties("Password") = "password"
    
    'Open Connection
    conn.Open ' strConn
    conn.Properties("Current Catalog").Value = "master"
    
    Dim rs As New ADODB.Recordset
    rs.ActiveConnection = conn
    
    SQLQuery = "declare @alias varchar(100), " + _
            "@sql varchar(1000) " + _
        "set @alias = 'Since ' + convert (varchar, GetDate() - 7, 101) " + _
        "set @sql = 'Select name as [' + @alias + '] from sysobjects' " + _
        "exec (@sql)"
        
    'Execute query
    rs.Open SQLQuery
    
    'Copy result to desired location
    Dim CopyStartCell As Range
    Set CopyStartCell = Range("A1")
    
    '********* Write header record
    Dim colOffset As Integer
    For colOffset = 0 To rs.Fields.Count - 1
        CopyStartCell.Offset(0, colOffset).Value = rs.Fields(colOffset).Name
    Next
    
    Set CopyStartCell = CopyStartCell.Offset(1, 0)
    CopyStartCell.CopyFromRecordset rs
    rs.Close
    
End Sub

Open in new window

You need to add reference to Microsoft ActiveX Data Object Library to use ADO.
And EXTRA care needed while forming the dynamic SQL string.

Thanks,
Harish

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mdreedAuthor Commented:
Harish

Looks like we have a winner.  Either solution looks pretty viable.  Thanks for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.