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?
 
Harish VargheseConnect With a Mentor Project 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
0
 
momi_sabagCommented:
you can't use an expression for an alias
0
 
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
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
mdreedAuthor Commented:
Harish

Works great as a script, thanks.  Is there any way to use the code in an SQL View?
0
 
momi_sabagCommented:
you can't use it in a view
0
 
momi_sabagCommented:
you can try to create a user function with that code,
and then create a  view that queries the user function
0
 
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
0
 
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.
0
 
mdreedAuthor Commented:
Harish

Looks like we have a winner.  Either solution looks pretty viable.  Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.