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
>

combine update statements....help...

Hi guys! Is there a way to combine these update statements?

Dim update_phase As New SqlCommand("INSERT INTO TE_shounin_zangyou (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_zangyou WHERE [syain_No] = @.syain_No", cnn)

Dim update_phase2 As New SqlCommand(" UPDATE TE_shounin_zangyou SET " & " phase=2, phase_states2=06,syounin2_sysd=CONVERT(VARCHAR(10),GETDATE(),101) WHERE [syain_No] = @.syain_No", cnn)

The same table is updated so I think it would be better to have just one update statement. But the problem is that, the first update statement retrieves values from another table, whereas the update values of the second statement is fixed. Is there a way to combine these two statements. I tried to do so but it does not update. Here's my code...

Dim update_phase As New SqlCommand("UPDATE TE_shounin_zangyou SET TE_shounin_zangyou.syain_No=TE_zangyou.syain_No, TE_shounin_zangyou.date_kyou=TE_zangyou.date_kyou, TE_shounin_zangyou.time_kyou=TE_zangyou.time_kyou FROM TE_zangyou WHERE TE_zangyou.syain_No = TE_shounin_zangyou.syain_No", cnn)

Please help me. Thanks.

Audrey

You can do it in one statement. Understand the consequences first. Lets say you already have some records (say 5) in table TE_shounin_zangyou, your first INSERT will add some more rows to it. Your second UPDATE will update the rows from the insert as well as the existing rows. However, if you combine both the INSERT and the UPDATE into one statement you will only modofy the rows being INSERTED with the SELECT statement. Any pre-existing rows will not be affected. If, in your case, there would be NO pre-existing rows with the condition [syain_No] = @.syain_No, then you can do it all in one statement as follows:

Try this:

INSERT INTO TE_shounin_zangyou (syain_No,date_kyou,time_kyou,phase,phase_states2,syounin2_sysd)

SELECT syain_No,date_kyou,time_kyou,2,'06',CONVERT(VARCHAR(10),GETDATE(),101) FROM TE_zangyou WHERE [syain_No] = @.syain_No

Combine record

Hi guys..
is there any query to do this action:
i want to combine view record into a single record.
exm.

table 1
Name A B
Jack 10 22
jack 12 21
jack ... ...
jack 1 11
ben 12 2
ben 3 2
ben ... ...

into:
View 1
Name combine
jack 10,22 and 12,21and1,11 and ....
ben 12,2 and 3,2 and.....

thx before..dede (neolempires2@.gmail.com) writes:

Quote:

Originally Posted by

is there any query to do this action:
i want to combine view record into a single record.
exm.
>
table 1
Name A B
Jack 10 22
jack 12 21
jack ... ...
jack 1 11
ben 12 2
ben 3 2
ben ... ...
>
>
into:
View 1
Name combine
jack 10,22 and 12,21and1,11 and ....
ben 12,2 and 3,2 and.....

Check out http://www.projectdmx.com/tsql/rowconcatenate.aspx for
suggestions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql

column names of the table

Hi guys,

Is there any function that can the column names of the table? I know about the sp_help but I want I'm going to call this from my .net application?

Thanks

How about using the INFORMATION_SCHEMA.COLUMNS view? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:2ff402f1-91fb-4795-aee4-bc05d3efcffa@.discussions.microsoft.com...
> Hi guys, >
> Is there any function that can the column names of the table? I know
> about the sp_help but I want I'm going to call this from my .net
> application? > > >
> Thanks >
>|||try this
select column_name from information_schema.columns where table_name ='agents'|||Thank guys!!!

Collation Sequence questions

Hi,

i have several questions about collation sequence i hope you guys will be able to help me with.

First :
If i have a collaction, let's say French_BIN ou SQL_Latin1_General_CP1_CI_AS, how do i know how to understand it ? I mean, is it Case Sensitive, Binary sort, Which page code, ... ?

Second :
I have 2 Databases. One using a collation X another using a collation Y.
I want to export the tables (data + keys) from one Database to another.
As far as i've noticed, not only tables are exported, but so is the collation. The result ? The two Databases appear to be using collation X. Am i missing something ?1. For French collation the code page is default 1252, refer to books online for more information.

BOL refers SQL Server supports having multiple, language-specific copies of the error messages stored in master.dbo.sysmessages. All instances of SQL Server contain the set of English messages. SQL Server is localized, or translated, into French, German, Spanish, and Japanese versions. Installations of localized versions of SQL Server install the translated set of messages in addition to the English set. When SQL Server sends a message to a connection, it uses the localized message if the language ID of the connection matches one of the language IDs present in sysmessages. If there is no message in sysmessages with the same language ID, the English version of the message is sent.

1988-2002 Microsoft Corporation. All Rights Reserved.

2. From SQL 2000 you can have column/table/database wide collation settings. So if you export the X collation to a Y collation database, only exported table will have X collation but not on database. Still it permits Y collation on database level, but X collation on table level.

Refer to books online for all kind of information about Collations.

HTH|||How can I transform the collation of an exported base?
In the properties,we don't have any more access to the list of collation,|||In SQL 2K you can change collation by using ALTER DATABASE... ALTER TABLE ... ALTER COLUMN on the database.|||How can I make the tables inherit of the database properties when I make an alter database?|||If you affect the collation to DATABASE level, the new data will be affected but not the existing data. To do so you should use ALTER TABLE to do on table level.

Do refer to Books online for COLLATION and other information.|||thanks for all