Joining 2 fields into 1 Thru SQL

[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.

[wildfrog]

Like this?

SELECT lastname + ', ' + firstname as fullname
FROM table

- petter

[Shasta80]

Really its that easy? I'm going to go modify my SELECT statement.

Thx

[Shasta80]

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

[wildfrog]

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

[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.

[pweegar]

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.

[endyk]

use

SELECT lastname + ', ' + firstname as 'full name'
FROM table

This works for SQL Server

[f_eriksen]

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

[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.

[dglienna]

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.

[cjard]

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"