Solved

Can SQL column alias be an expression?

Posted on 2012-04-13
9
639 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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…

735 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