We help IT Professionals succeed at work.

Combining columns in MSSQL query

TPBPIT
TPBPIT asked
on
288 Views
Last Modified: 2010-04-21
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.
Comment
Watch Question

Commented:
SELECT (ColA + ',' + ColB + ',' + ColC) As FullName, ColD, ColE

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

Commented:
Sorry, I didn't spot the last part of your comment

ORDER BY 1 ASC

Commented:
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

Open in new window

Author

Commented:
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.

Commented:
Woops, I see what I missed. I didn't put the second argument in for MiddleName.

COASLESCE is in SQL2000
SELECT (COALESCE(LastName,'') + ',' +  COALESCE(FirstName,'') + ' ' + COALESCE(MiddleName,'')) AS FullName, ColumnD, ColumnE
FROM Table
ORDER BY 1 ASC

Open in new window

Author

Commented:
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?

Commented:
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.
SELECT (COALESCE(LastName,'') + ',' +  COALESCE(FirstName,'') + ' ' + COALESCE(MiddleName,''), COALESCE(', ' + Suffix,'')) AS FullName, ColumnD, ColumnE
FROM Table
ORDER BY 1 ASC

Open in new window

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for all your help.  Just got back in from lunch and this worked like a champ.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.