Solved

Can SQL column alias be an expression?

Posted on 2012-04-13
9
626 Views
Last Modified: 2012-04-13
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'
0
Comment
Question by:mdreed
  • 3
  • 3
  • 3
9 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37842713
you can't use an expression for an alias
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 37842793
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
 

Author Comment

by:mdreed
ID: 37842849
Harish

Works great as a script, thanks.  Is there any way to use the code in an SQL View?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37842870
you can't use it in a view
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 37842872
you can try to create a user function with that code,
and then create a  view that queries the user function
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 37843519
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
 

Author Comment

by:mdreed
ID: 37843604
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
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 500 total points
ID: 37844359
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
 

Author Closing Comment

by:mdreed
ID: 37844381
Harish

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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now