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
Solved

Is a dynamic column alias possible?

Posted on 2004-03-26
4
5,590 Views
Last Modified: 2011-06-17
Hello,

I run a stored procedure every quarter that has one input parameter, the current date.  

For one data item, values are secured for the current and past 11 quarters.  The table column names for this field are:  Current, Current_Minus1, Current_Minus2…Current_Minus11.  

I would like for my output to have different “quarter names” for the columns instead of the actual column names.  

A hard-coded “AS” Statement works great, for example:
Select Current as 1Q04, Current_Minus1 as 4Q03, Current_Minus2 as 3Q03, etc.
But I do not want to change this every quarter.

Is there a type of variable that can be used instead of the hard-coded values???  I would like to calculate the column names and place them in a variable so that I could code something like:

“Select Current as @CurrentQtr, Current_Minus1 as @CurrentQtrMinus1, Current_Minus2 as @CurrentQtrMinus2, … “

I have attempted this approach using several differnt variable types and always receive an error.  Is there a specific variable type that works or appropriate syntax to make this possible??  If not - Any other ideas that might make this work?

Down the road I will be creating an Access front-end screen that will allow users to create this report themselves for a specific current date by clicking a button that will execute the stored procedure generate a spreadsheet.  If the approach above is not possible in SQL perhaps it would be possible in Access??

Thank you for your assistance!

TXAmmonite (David)
0
Comment
Question by:TXAmmonite
  • 2
4 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10690391
this is a presentation issue and you should get your GUI / Frontend to
name the columns appropriately...

that should be possible in access


it is possible in SQL Server by using dynamic sql
but i would recommend it's use for this scenario...  

e.g.
declare SQLSTR varchar(8000)

set @sqlstr= “Select Current as @CurrentQtr, Current_Minus1 as @CurrentQtrMinus1, Current_Minus2 as @CurrentQtrMinus2, … “

Exec (@SQLSTR)


hth


0
 

Author Comment

by:TXAmmonite
ID: 10692100
hth,

I wish it were that simple - I had already tried this approach.  
All variables where declared as varchars and populated (selected each to verify)

Created the dynamic SQL as:
set @SQLSTR= 'Select Current as @Curr_Qtr,
Current_Minus1 as @Curr_QDate_Minus1,
Current_Minus2 as @Curr_QDate_Minus2
from Tbl_MyTablel'
Exec (@SQLSTR)

I received the error:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@Curr_Qtr'.

After removing the first "@" the error would change to
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@Curr_QDate_Minus1'.

Removing all "@"s allowed the query to execute.  

Still looking for that special variable type or format.

Thanks,
David
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 125 total points
ID: 10692639
try this please:

declare @SQLSTR varchar(2000)
declare @Curr_Qtr varchar(50)
declare  @Curr_QDate_Minus1 varchar(50)
declare  @Curr_QDate_Minus2 varchar(50)
set  @Curr_Qtr ='1Q04'
set  @Curr_QDate_Minus1 ='2Q04'
set  @Curr_QDate_Minus2 ='3Q04'
set @SQLSTR= 'Select [Current] as [' + @Curr_Qtr + '],
Current_Minus1 as [' + @Curr_QDate_Minus1 + '],
Current_Minus2 as [' + @Curr_QDate_Minus2 + ']
from Tbl_MyTablel'

--print @SQLSTR
exec(@SQLSTR)
0
 

Author Comment

by:TXAmmonite
ID: 10704412
EugeneZ

Thanks - this worked great!
TXAmmonite
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

839 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