Combining two Columns from a View

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combining two Columns from a View

Author

Topic

Tade
Starting Member

5 Posts

Posted - 2010-10-18 : 09:10:02

Hey all.
I wrote a small script to create a View that displays two tables from two different databases, using Inner Joins and Unions commands.

The result columns were as follows:

ID FirstName LastName

What I need to do now, is in that same View combine column 2 and 3 (FirstName and LastName) into a new column called FullName, and have the result display only the ID and FullName column, as follows:

ID FullName

I need it all to be in one script as I'm going to save the View itself and use it later in a C# Project.

Thanks in advance for the help!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-18 : 09:16:14

You can concatenate the two columns to get the computed column.

Example
isnull(Firstname + ' ','') + isnull(LastName , '') as FullName

Tade
Starting Member

5 Posts

Posted - 2010-10-18 : 09:56:32

Thanks for the fast reply, but I'm not sure I got it.
I don't understand where to insert that line. Perhaps if I wrote down the script you'd be able to help me a bit more? Here it is:

SELECT DISTINCT Library.dbo.tblOutsiders.ID, Library.dbo.tblOutsiders.FirstName COLLATE Hebrew_CI_AS,
Library.dbo.tblOutsiders.LastName COLLATE Hebrew_CI_AS
FROM Library.dbo.tblOutsiders CROSS JOIN Org.dbo.tblPerson
UNION
SELECT DISTINCT Org.dbo.tblPerson.ID, Org.dbo.tblPerson.FirstName COLLATE Hebrew_CI_AS, Org.dbo.tblPerson.LastName COLLATE Hebrew_CI_AS
FROM Library.dbo.tblOutsiders CROSS JOIN Org.dbo.tblPerson

When I execute that query, I get the results that I need to combine together into one column.
Where do I write the command line you described?

Again, thanks in advance.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-18 : 13:31:30

Try this:

SELECT DISTINCT L.ID,
L.FirstName COLLATE Hebrew_CI_AS,
L.LastName COLLATE Hebrew_CI_AS,
Isnull(L.FirstName COLLATE Hebrew_CI_AS + ' ','') + Isnull(L.FirstName COLLATE Hebrew_CI_AS ,'') as FullName
FROM Library.dbo.tblOutsiders L CROSS JOIN Org.dbo.tblPerson P
UNION
SELECT DISTINCT O.ID,
O.FirstName COLLATE Hebrew_CI_AS,
O.LastName COLLATE Hebrew_CI_AS,
Isnull(O.FirstName COLLATE Hebrew_CI_AS + ' ','') + Isnull(O.FirstName COLLATE Hebrew_CI_AS ,'') as FullName
FROM Library.dbo.tblOutsiders O CROSS JOIN Org.dbo.tblPerson T

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Tade
Starting Member

5 Posts

Posted - 2010-10-19 : 04:56:47

That worked great. After reading up on everything you said I now understand what it does, too, instead of just copy & pasting.
Thanks for the help!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-19 : 06:27:13

quote:
Originally posted by Tade

That worked great. After reading up on everything you said I now understand what it does, too, instead of just copy & pasting.
Thanks for the help!

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources