aliasing two columns in SQL

[Google Jenny]

Hi,

Here is my original query:

select rosterid, lastname, firstname from table
order by lastname
I would like to use column aliasing to display
lastname, firstname in a column entitled name.

I tried the following syntax, but it's not working:

select rosterid, lastname+', '+firstname as name
from table
order by name

This results in a 2 column table with the headings "ROSTERID" and
"NAME". However, NAME contains th last name only, rather than "lastname,
firstname".

Any help greatly appreciated.

Thanks,
Google Jeny
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ryan]

One thing worth looking at is not using reserved words such as Name.
You need to change this to something different.

R

Google Jenny <mi******@yahoo.com> wrote in message news:<41**********************@news.newsgroups.ws> ...

Hi,

Here is my original query:

select rosterid, lastname, firstname from table
order by lastname
I would like to use column aliasing to display
lastname, firstname in a column entitled name.

I tried the following syntax, but it's not working:

select rosterid, lastname+', '+firstname as name
from table
order by name

This results in a 2 column table with the headings "ROSTERID" and
"NAME". However, NAME contains th last name only, rather than "lastname,
firstname".

Any help greatly appreciated.

Thanks,
Google Jeny
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

Google Jenny <mi******@yahoo.com> wrote in message news:<41**********************@news.newsgroups.ws> ...

Hi,

Here is my original query:

select rosterid, lastname, firstname from table
order by lastname
I would like to use column aliasing to display
lastname, firstname in a column entitled name.

I tried the following syntax, but it's not working:

select rosterid, lastname+', '+firstname as name
from table
order by name

This results in a 2 column table with the headings "ROSTERID" and
"NAME". However, NAME contains th last name only, rather than "lastname,
firstname".

Any help greatly appreciated.

Thanks,
Google Jeny
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

This query works for me in pubs:

select au_id, au_lname + ', ' + au_fname as 'name'
from authors
order by 'name'

Perhaps you could post your CREATE TABLE statement and some sample data?

Simon

[Matt Piselli]

You may want to add the following as well to remove any additional
spaces in the name fields:

select rosterid, rtrim(lastname) +', ' + rtrim(firstname) as Name
from table

[Google Jenny]

Thanks so much. The rtrim did the trick.

Google Jenny

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!