Joining 2 fields into 1 Thru SQL
Thank you.
[161 byte] By [Shasta80] at [2007-11-19 21:42:29] 
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.
# 1 Re: Joining 2 fields into 1 Thru SQL
Like this?SELECT lastname + ', ' + firstname as fullname
FROM table
- petter
wildfrog at 2007-11-9 13:43:33 >
# 2 Re: Joining 2 fields into 1 Thru SQL
Really its that easy? I'm going to go modify my SELECT statement.Thx
Shasta80 at 2007-11-9 13:44:34 >
# 3 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
Shasta80 at 2007-11-9 13:45:44 >
# 4 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 ida), 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
wildfrog at 2007-11-9 13:46:38 >
# 5 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.
Shasta80 at 2007-11-9 13:47:38 >
# 6 Re: 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.
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.
pweegar at 2007-11-9 13:48:48 >
# 7 Re: Joining 2 fields into 1 Thru SQL
useSELECT lastname + ', ' + firstname as 'full name'
FROM table
This works for SQL Server
endyk at 2007-11-9 13:49:41 >
# 8 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.
use brackets []. sql server can not misunderstand :)
select a as [my new colname] from tab
f_eriksen at 2007-11-9 13:50:45 >
# 9 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.
Imdabaum at 2007-11-9 13:51:43 >
# 10 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.dglienna at 2007-11-9 13:52:45 >
# 11 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.
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"
cjard at 2007-11-9 13:53:43 >
