Avatar of TPBPIT
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.
SQL

Avatar of undefined
Last Comment
TPBPIT

8/22/2022 - Mon
CCongdon

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
CCongdon

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

ORDER BY 1 ASC
CCongdon

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
TPBPIT

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

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

ASKER
TPBPIT

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
CCongdon

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

ASKER CERTIFIED SOLUTION
CCongdon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
TPBPIT

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