How to set an Alias

[Bamboosch]

Hi there,

i think, it´s a very simple problem.

how can i set an alias of a column by a variable ???


this will not work:

DECLARE @pID int
SET @pID=1015

SELECT customer AS @pID FROM ...

is there a way ?

thanks ;-)
bamboosch

 

[foxdev]

As far as I can tell, column names or other identifiers cannot start with a letter; they must start with a letter or the underscore.

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

Simply get rid of the @ in the alias name...

Tom

 

[Bamboosch]

it don´t work without the @,
i need the content not the name of the variable...

and it´s the same with letters instead of numbers...

cst

 

[TomSark]

Ahhh.. sorry, I misunderstood the quesiton...

You could use execute to build the select string and execute the string as in:

USE PUBS
GO
DECLARE @sql_str varchar(255)

SET @pID='A015' -- cant start with a number, so I made it start with a char.

Set @sql_str = 'SELECT au_lname AS ' + convert(varchar(30), @pID) + ' FROM authors'

execute(@sql_str)

But note, however, that an alias cannot start with a number, it must start with an alphabetic character.

Thanks,

Tom

 

[TomSark]

And.. i forgot to add the @pid declaration in the code above... geesh.. having a bad day... stayed up too late last night watching the election...


Tom

 

[Bamboosch]

Oh yes, many thanks for your help!

Also I found a solution to use only numbers in column names:

USE PUBS
GO
DECLARE @sql_str varchar(255)
DECLARE @pid varchar(30)

SET @pID='[15]' -- If you use [ ] brackets inside the ', numbers are possible, too.

SET @sql_str = 'SELECT au_lname AS ' + convert(varchar(30), @pID) + ' FROM authors'

EXECUTE(@sql_str)



without your tip it would have been a very very long night... ;-)

bamboosch

 

[TomSark]

Glad to help... name a kid after me or something LOL

Tom