combining 2 or more columns "as" one column in a select statement

hi guys,
how do u combine 2 columns as one in a select statement such as:
"select firstname,lastname from tablename"
where firstname and lastname are to be combined as single column called name
.
i had difficulty finding other resources for this issue.
Thanks in advance,
AdsString concatenation. The + symbol concatenates strings in SQL. So
your example would be something like:
select rtrim(firstname) + ' ' + rtrim(lastname) as [name] from
tableowner.tablename
or
select rtrim(lastname) + ', ' + rtrim(firstname) as [name] from
tableowner.tablename
The rtrim() function just trims off trailing whitespace (I'm assuming
there wont be any leading whitespace but you can take care of that too
with ltrim()), so you don't end up with something like "John
Smith " or "Smith , John ". The rest just depends exactly
what string you want at the end of it.
*mike hodgson*
http://sqlnerd.blogspot.com
ads wrote:

>hi guys,
>how do u combine 2 columns as one in a select statement such as:
>"select firstname,lastname from tablename"
>where firstname and lastname are to be combined as single column called nam
e.
>i had difficulty finding other resources for this issue.
>Thanks in advance,
>Ads
>
>|||SELECT fname + ' ' + lname as FulName from names
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"ads" <ads@.discussions.microsoft.com> wrote in message
news:53BE5122-5439-4101-A221-E3FA011C6C24@.microsoft.com...
> hi guys,
> how do u combine 2 columns as one in a select statement such as:
> "select firstname,lastname from tablename"
> where firstname and lastname are to be combined as single column called
> name.
> i had difficulty finding other resources for this issue.
> Thanks in advance,
> Ads
>|||Just to throw my $.02 in, I'd suggest doing NULL checks on any string concat
in SQL server.
So my version would look like:
SELECT ISNULL(FirstName, '') + ' ' + ISNULL(LastName, '')
FROM TableName
Now if you want to get fancy you could throw a check in there to only output
the space if both names are present. At that point though I might use a udf
SELECT dbo.udfGetFullName(TablePK)
FROM TableName
Where the udf performs the logic required to properly format the name.
"ads" wrote:

> hi guys,
> how do u combine 2 columns as one in a select statement such as:
> "select firstname,lastname from tablename"
> where firstname and lastname are to be combined as single column called na
me.
> i had difficulty finding other resources for this issue.
> Thanks in advance,
> Ads
>

Column prefix doesnt match with a table name error

I keep getting an error of Column prefix " t" doesnt match with a table
name or alias name with the query with this sql query (part of a c#
script):
"SELECT
d.SalesRep_id,s.fname,s.lname,s.Total_BonusPerc,s.ID,s.Hire_date,s.Term_date
,
d.ddate,d.salesOff_loc_ID,d.SplitGross,
d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,c.ID,t.Amount_Revenue,t.MGTag_ID
FROM tblBankTran as t,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
= d.SalesRep_id INNER JOIN tblCustTag as c on c.ID = t.MGTag_ID WHERE
d.salesOff_loc_ID = " + 3 + "";
I'm trying to include the tblBankTran table and it's Amount_Revenue,
which the tblBanktran table field MGTag_ID is related to tblCustTag ID
field.
Thanks in advance
.NetSportsHi
This is invalid syntax
FROM tblBankTran as t,tblDeal as d INNER JOIN
Instead , FROM tblBankTran t INNER JOIN tblDeal s ON....
For details please refer to the BOL
".Net Sports" <ballz2wall@.cox.net> wrote in message
news:1118099682.466063.5530@.g43g2000cwa.googlegroups.com...
> I keep getting an error of Column prefix " t" doesnt match with a table
> name or alias name with the query with this sql query (part of a c#
> script):
> "SELECT
>
d.SalesRep_id,s.fname,s.lname,s.Total_BonusPerc,s.ID,s.Hire_date,s.Term_date
,
> d.ddate,d.salesOff_loc_ID,d.SplitGross,
> d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,c.ID,t.Amount_Revenue,t.MGTag_ID
> FROM tblBankTran as t,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
> = d.SalesRep_id INNER JOIN tblCustTag as c on c.ID = t.MGTag_ID WHERE
> d.salesOff_loc_ID = " + 3 + "";
> I'm trying to include the tblBankTran table and it's Amount_Revenue,
> which the tblBanktran table field MGTag_ID is related to tblCustTag ID
> field.
> Thanks in advance
> .NetSports
>