aliasing two columns in SQL

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!One thing worth looking at is not using reserved words such as Name.
You need to change this to something different.

R

Google Jenny <michiamo@.yahoo.com> wrote in message news:<412cfc29$0$14430$c397aba@.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!|||Google Jenny <michiamo@.yahoo.com> wrote in message news:<412cfc29$0$14430$c397aba@.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|||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|||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!