-
May 14th, 2006, 10:53 AM
#1
Joining 2 fields into 1 Thru SQL
Is it possible utilizing a SQL statement to take 2 fields, such as firstname and lastname into another field called fullname combining the 2?
Thank you.
-
May 14th, 2006, 11:49 AM
#2
Re: Joining 2 fields into 1 Thru SQL
Like this?
Code:
SELECT lastname + ', ' + firstname as fullname
FROM table
- petter
-
May 14th, 2006, 02:58 PM
#3
Re: Joining 2 fields into 1 Thru SQL
Really its that easy? I'm going to go modify my SELECT statement.
Thx
-
May 14th, 2006, 03:05 PM
#4
Re: Joining 2 fields into 1 Thru SQL
Hey that worked great. May I ask another question? Are you not allowed to have spaces when naming columns.
Your example
SELECT lastname + ', ' + firstname as fullname
FROM table
Works perfect. I tried to give 'fullname' as 'Full Name' but generates an error, obviously because of the space, is there a way around this?
Thank you
-
May 14th, 2006, 03:19 PM
#5
Re: Joining 2 fields into 1 Thru SQL
I'm not sure if the is a standard way of doing this (not sure if I even like the idéa), but I belive that you can do this with apostophe (') or brackets ([]) in MS SQL, and back quote (`) in mySQL.
What database are you targeting?
- petter
-
May 14th, 2006, 03:29 PM
#6
Re: Joining 2 fields into 1 Thru SQL
Long story short, I've been shortlisted for an entry level position and they have asked to provide 2 solutions as a test prior to the interview. But its for Transact SQL so I will assume Windows SQL Server.
The naming convention is not a big deal, I was just unsure how to create the one field from two thru SQL. If I couldn't do it on the fly, I was just going to add a field in the table itself and query that.
-
May 16th, 2006, 09:22 AM
#7
Re: Joining 2 fields into 1 Thru SQL
Originally Posted by Shasta80
Is it possible utilizing a SQL statement to take 2 fields, such as firstname and lastname into another field called fullname combining the 2?
Thank you.
Is this to DISPLAY the combined 2 fields or do you need to add another field to your table, called fullname, and combine the two fields? IF you need to add a new field then you would need to use the alter table command, and set up this new field. Then do a update on the new field.
-
May 16th, 2006, 02:38 PM
#8
Re: Joining 2 fields into 1 Thru SQL
use
SELECT lastname + ', ' + firstname as 'full name'
FROM table
This works for SQL Server
-
May 17th, 2006, 09:04 AM
#9
Re: Joining 2 fields into 1 Thru SQL
Originally Posted by Shasta80
Long story short, I've been shortlisted for an entry level position and they have asked to provide 2 solutions as a test prior to the interview. But its for Transact SQL so I will assume Windows SQL Server.
The naming convention is not a big deal, I was just unsure how to create the one field from two thru SQL. If I couldn't do it on the fly, I was just going to add a field in the table itself and query that.
use brackets []. sql server can not misunderstand
select a as [my new colname] from tab
-
July 5th, 2006, 03:29 PM
#10
Re: Joining 2 fields into 1 Thru SQL
I understand you can join several fields into 1 thru SQL. Is there a way to join several records with similar ids into a table?
I have a table that holds projectIDs and Notes for each project. Another table Updates holds projectIDs and individual notes made about the project. I was wondering if there was an easy way to set Notes to the concatination of all individual notes.
-
July 5th, 2006, 06:18 PM
#11
Re: Joining 2 fields into 1 Thru SQL
You would have to run a query that used one of the JOIN options. You can select records from either one, or both tables. Then, you'd have a recordset that contained all the fields you wanted.
-
July 13th, 2006, 06:02 AM
#12
Re: Joining 2 fields into 1 Thru SQL
Originally Posted by Imdabaum
I understand you can join several fields into 1 thru SQL. Is there a way to join several records with similar ids into a table?
I have a table that holds projectIDs and Notes for each project. Another table Updates holds projectIDs and individual notes made about the project. I was wondering if there was an easy way to set Notes to the concatination of all individual notes.
No. Turning an N-long result column into a single concatenated string with all the values requires programming logic such as stored procedure or client. If you could guarantee that there would only ever be a max of 10 notes I can give you a wordy sql that will do it, but be aware it requires significant effort, first splitting the results from a column:
#
#
#
into a diagonal table:
#__
_#_
__#
into a flat:
# # #
into a concatenated:
###
and the number of rows has to be known in advance
Be careful using "join" to mean "concatenate"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|