TPBPIT
asked on
Combining columns in MSSQL query
I have several columns called for simplicity A, B, C, D and E that are all in the same table. I need to join A, B, and C into one column called FullName and still have D and E in separate columns. I can get A, B, and C into a new column with the following select statement: Select(LastName + ', ' + FirstName + ' ' + MiddleName) As FullName. However, I can't figure out how to get D and E into the select statement to have them put in there own columns. Any idea?
Also, the resulting dataset needs to be in ascending order by the newly created column FullName.
Also, the resulting dataset needs to be in ascending order by the newly created column FullName.
Last Comment
Sorry, I didn't spot the last part of your comment
ORDER BY 1 ASC
ORDER BY 1 ASC
Here, let me make the whole thing a little more legible.
SELECT (COALESCE(LastName,'') + ',' + COALESCE(FirstName,'') + ' ' + COALESCE(MiddleName)) AS FullName, ColumnD, ColumnE
FROM Table
ORDER BY 1 ASC
ASKER
Not working. This is the full query I have:
SELECT (COALESCE(LastName,'') + ',' + COALESCE(FirstName,'') + ' ' + COALESCE(MiddleName)) AS FullName, BusinessTelephone, Spouse
FROM Table
ORDER BY 1 ASC
And this is the error query analyzer is giving:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
I didn't state this in the original question, but this is MSSQL 2000.
SELECT (COALESCE(LastName,'') + ',' + COALESCE(FirstName,'') + ' ' + COALESCE(MiddleName)) AS FullName, BusinessTelephone, Spouse
FROM Table
ORDER BY 1 ASC
And this is the error query analyzer is giving:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
I didn't state this in the original question, but this is MSSQL 2000.
Woops, I see what I missed. I didn't put the second argument in for MiddleName.
COASLESCE is in SQL2000
COASLESCE is in SQL2000
SELECT (COALESCE(LastName,'') + ',' + COALESCE(FirstName,'') + ' ' + COALESCE(MiddleName,'')) AS FullName, ColumnD, ColumnE
FROM Table
ORDER BY 1 ASC
ASKER
One last question. After MiddleName I would like to add ", Suffix". That would read comma space Suffix, but I only want the comma and space being added if there was a value in the Suffix field. Can that be done?
This time, we're using the String + NULL = NULL to our advantage. If the suffix field is NULL then adding the comma to it will result in a null value. Then the COALESCE will choose the empty string instead of the NULL.
The only problem is that if you have an empty string instead of NULL. You'd have to do something slightly different for this SQL to work right. I'd highly suggest allowing/using NULLS.
The only problem is that if you have an empty string instead of NULL. You'd have to do something slightly different for this SQL to work right. I'd highly suggest allowing/using NULLS.
SELECT (COALESCE(LastName,'') + ',' + COALESCE(FirstName,'') + ' ' + COALESCE(MiddleName,''), COALESCE(', ' + Suffix,'')) AS FullName, ColumnD, ColumnE
FROM Table
ORDER BY 1 ASC
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for all your help. Just got back in from lunch and this worked like a champ.
SQL
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
However, I suggest you look at using Coalesce to filter the columns you're adding togehter. If you have a NULL in any of those fields, the whole thing would be NULL...
SELECT (COALESCE(ColA,'') + ',' + COALESCE(ColB,'') + ',' + COALESCE(ColC)) AS FullName, ColD, ColE
FROM Table