Solved

Can SQL column alias be an expression?

Posted on 2012-04-13
9
643 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 37842870
you can't use it in a view
0
 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql Query join multiple table and distinct records 7 32
How can I exclude some wording in a like statement? 39 83
Sql query 107 100
Find results from sql within a time span 11 56
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

732 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