asked on

SQL Set query to combine field values

I have a table dbo.mdx_physdirectory with 6 fields:
specialty0
specialty1
specialty2
specialty3
specialty4
specialty5

When I look at the table in SQL, some of the fields say NULL and some are just blank.

I ran the following update query to combine specialty fields 1-5 (separated by commas) into a single field (specialty0)

UPDATE mdx_physdirectory
SET specialty0 = specialty1 + ', ' + specialty2 + ', ' + specialty3 + ', ' + specialty4 + ', ' + specialty5
WHERE (specialty1 IS NULL) OR
(specialty1 = '') OR
(specialty2 IS NULL) OR
(specialty2 = '') OR
(specialty3 IS NULL) OR
(specialty3 = '') OR
(specialty4 IS NULL) OR
(specialty4 = '') OR
(specialty5 IS NULL) OR
(specialty5 = '')

When I run the query, though, only the rows where one or more of the specialty fields (1-5) =blank update and those where one or more of the specialty fields (1-5) = Null do not update. Also, there are commas with blank spaces wherever any of the fields were blank.

Can someone please tell me how to get all the fields to update (even if some of the fields =blank or =Null) and how to not include commas when the field is blank or null?

Thanks.

Ronak Patel

hey.. try below Query:

UPDATE mdx_physdirectory
SET specialty0 = ISNULL(specialty1,'') + ', ' + ISNULL(specialty2,'') + ', ' + ISNULL(specialty3,'') + ', ' + ISNULL(specialty4,'') + ', ' + ISNULL(specialty5,'')
WHERE 
ISNULL(specialty1,'') = '' OR
ISNULL(specialty2,'') = '' OR
ISNULL(specialty3,'') = '' OR
ISNULL(specialty4,'') = '' OR
ISNULL(specialty5,'') = ''

Open in new window

Geert G

you have to check the previous and next field when adding the comma

UPDATE mdx_physdirectory
SET specialty0 =
CASE WHEN specialty1 is NULL OR specialty1 = '' THEN '' else specialty1 +
CASE WHEN
specialty2 is NOT NULL OR specialty2 <> '' OR
specialty3 is NOT NULL OR specialty3 <> '' OR
specialty4 is NOT NULL OR specialty4 <> '' OR
specialty5 is NOT NULL OR specialty5 <> '' THEN ',' else '' end end +
CASE WHEN specialty2 is NULL OR specialty2 = '' THEN '' else specialty2 +
CASE WHEN
specialty3 is NOT NULL OR specialty3 <> '' OR
specialty4 is NOT NULL OR specialty4 <> '' OR
specialty5 is NOT NULL OR specialty5 <> '' THEN ',' else '' end end +
CASE WHEN specialty3 is NULL OR specialty3 = '' THEN '' else specialty3 +
CASE WHEN
specialty4 is NOT NULL OR specialty4 <> '' OR
specialty5 is NOT NULL OR specialty5 <> '' THEN ',' else '' end end +
CASE WHEN specialty4 is NULL OR specialty4 = '' THEN '' else specialty4 +
CASE WHEN
specialty5 is NOT NULL OR specialty5 <> '' THEN ',' else '' end end +
CASE WHEN specialty5 is NULL OR specialty5 = '' THEN '' else specialty5 end
WHERE
ISNULL(specialty1,'') = '' OR
ISNULL(specialty2,'') = '' OR
ISNULL(specialty3,'') = '' OR
ISNULL(specialty4,'') = '' OR
ISNULL(specialty5,'') = ''

kimberlys777

ASKER

Thanks for the replies.
Ronak_Patel - I tried this, but it still had the same results as I had gotten before.

Geert_Gruwez - This solution worked well, but I still have a final comma on the end of the last field if the next field is blank (=' ') but no comma on the end of the last field if the next field is null. A pic of two SQL rows is attached

Thanks for all your help!
sql-comma.gif

SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geert G

fwiw,
you'll have to think about a redesign when somebody comes along with 7, 8, 9, 10, 21 specialtys ...

ASKER CERTIFIED SOLUTION

Ronak Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ronak Patel

I will also agree with Geert_Gruwez, that if the size of specialty will be a large number then this approach will be like mess and will include lot of labor work. It is advisable to re-design the requirement if the number of specialty is going to be changed/increased.

Thanks,
Ronak

Geert G

Ronak_Patel
>> optimized looks a lot like plagiarized
or did you mean formatted ?

if you don't have new information, then why post ?

Ronak Patel

I did change the condition of secondary "CASE" statement from "OR" to "AND" and it did change the out put.

I executed your SQL and I was still getting extra comma at the end if the field is blank.

refer result1.png I've attached.

Thanks,
Ronak
result1.png

Geert G

oh cool, now i see it too
guess i didn't apply the simpson rules completely

kimberlys777

ASKER

It works correctly now, thanks so much!

I selected Ronak_Patel's solution because it completed Geert_Gruwez' code by eliminating the ending comma, but since it was based on Geert_Gruwez's code, I'm awarding points to both. Thanks again, I really appreciate the help!

kimberlys777

ASKER

Thanks!