Need expression in column alias

Question

Post reply

Need expression in column alias

Joe-420121

Hall of Fame

Points: 3152
More actions
October 27, 2010 at 3:15 pm

#141211

I need to dynamically name the column in views something as below. How to do it
create view vvv
as
select
customer,
isnull(Day05,0) as '['+convert(varchar(10),getdate()-5,110)+']',
isnull(Day04,0) as '['+convert(varchar(10),getdate()-4,110)+']',
isnull(Day03,0) as '['+convert(varchar(10),getdate()-3,110)+']',
isnull(Day02,0) as '['+convert(varchar(10),getdate()-2,110)+']',
isnull(Day01,0) as '['+convert(varchar(10),getdate()-1,110)+']'
from Sales

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 1

pl some help me, ...any other idea...what is wrong with my stmt..I get "Incorrect syntax near '+'."

Lowell SSC Guru Points: 323518 More actions · Answer 2

Joe you cannot make the aliases for the columns dynamically(at least not directly) , so you have to revert to dynamic SQL to do it instead...building the command, then executing it like htis:

declare @cmd varchar(4000)

SET @cmd =

'

create view vvv

as

select

customer,

isnull(Day05,0) as [' + convert(varchar(10),getdate()-5,110) + '],

isnull(Day04,0) as [' + convert(varchar(10),getdate()-4,110) + '],

isnull(Day03,0) as [' + convert(varchar(10),getdate()-3,110) + '],

isnull(Day02,0) as [' + convert(varchar(10),getdate()-2,110) + '],

isnull(Day01,0) as [' + convert(varchar(10),getdate()-1,110) + ']

from Sales'

print @cmd

exec(@cmd)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 3

Joe,

I see two issues here.

First, you can't use an expression for a column alias. The query compiler will not evaluate your expression prior to its trying to execute the statement. You can do what you want by using dynamic SQL -- that is, putting your statement into a string/varchar variable, then executing that with the EXECUTE statement. Like this:

DECLARE @SqlStmt varchar(max)

SET @SqlStmt = 'create view vvv as select customer, ' +

'isnull(Day05,0) as ['+convert(varchar(10),getdate()-5,110)+'], ' +

'isnull(Day04,0) as ['+convert(varchar(10),getdate()-4,110)+'], ' +

'isnull(Day03,0) as ['+convert(varchar(10),getdate()-3,110)+'], ' +

'isnull(Day02,0) as ['+convert(varchar(10),getdate()-2,110)+'], ' +

'isnull(Day01,0) as ['+convert(varchar(10),getdate()-1,110)+'], ' +

'from Sales'

EXEC @SqlStmt

The second issue is to question why you're creating a view with column names that are dependent on the current date. Are you dropping and recreating this view on a daily basis? And referencing this view from multiple other queries and/or multiple times? If so, maybe it makes sense. Just wondering...

Rob Schripsema
Propack, Inc.

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 4

Wow -- Lowell beat me to the response. At least we came up with the same answer!

Rob Schripsema
Propack, Inc.

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 5

lowell and rob

thanks for your help. It looks like I am not thinking right. let me think and get back to you. i am not also able to use your ideas in my situation. Thanks anyway.

Ed B Hall of Fame Points: 3666 More actions · Answer 6

If its for some kind of report then you can go with generic column names like TodayDayMinus5 and dynamically name the column headers in the presentation layer/report.

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 7

TodayMinus4 is nice idea but still will not work.

This is my situation.

I have a table [sales] where I had to store the last 30 days of total sales to each customer every night for OLAP. I use the table in MS reporting services where I could easily modify the column header to show the last 30 days of Dates using FORMAT. The same table is used as VIEW also for other purposes where I have to change the column header to show the corresponding dates. So I am stuck here. The users will not understand the actual last 30 days as day01, day02….day31.

The issue is having dynamic dates the WHERE clause going to be an issue…?

I am still thinking..

Ed B Hall of Fame Points: 3666 More actions · Answer 8

Sounds like you have users with querying privileges without the skills they need. Good luck!

You may have to write some dynamic code to drop and recreate the view with new column names every night.

If you schedule it to run at midnight, it will appear to be dynamically named.

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 9

thanks perfect: "new column names every night" this will for me.