SQL Set query to combine field values

kimberlys777
kimberlys777 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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 GOracle dba
Top Expert 2009

Commented:
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,'') = ''

Author

Commented:
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
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Geert GOracle dba
Top Expert 2009
Commented:
blank or 1 space ?

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

Open in new window

Geert GOracle dba
Top Expert 2009

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

Here is the optimized SQL.

I have also attached the result (refer result.png).

UPDATE mdx_physdirectory
SET specialty0 =
	CASE WHEN specialty1 IS NULL OR RTRIM(LTRIM(specialty1)) = '' 
		THEN ''
	ELSE specialty1 +
		CASE WHEN
			(specialty2 IS NOT NULL AND RTRIM(LTRIM(specialty2)) <> '') OR
			(specialty3 IS NOT NULL AND RTRIM(LTRIM(specialty3)) <> '') OR
			(specialty4 IS NOT NULL AND RTRIM(LTRIM(specialty4)) <> '') OR
			(specialty5 IS NOT NULL AND RTRIM(LTRIM(specialty5)) <> '') 
			THEN ','
		ELSE '' 
		END 
	END +

	CASE WHEN specialty2 IS NULL OR RTRIM(LTRIM(specialty2)) = '' 
		THEN '' 
	ELSE specialty2 +
		CASE WHEN
			(specialty3 IS NOT NULL AND RTRIM(LTRIM(specialty3)) <> '') OR
			(specialty4 IS NOT NULL AND RTRIM(LTRIM(specialty4)) <> '') OR
			(specialty5 IS NOT NULL AND RTRIM(LTRIM(specialty5)) <> '') 
			THEN ',' 
		ELSE '' 
		END 
	END +
	
	CASE WHEN specialty3 IS NULL OR RTRIM(LTRIM(specialty3)) = '' THEN '' else specialty3 +
		CASE WHEN
			(specialty4 IS NOT NULL AND RTRIM(LTRIM(specialty4)) <> '') OR
			(specialty5 IS NOT NULL AND RTRIM(LTRIM(specialty5)) <> '') 
			THEN ',' 
		ELSE '' 
		END 
	END +

	CASE WHEN specialty4 IS NULL OR RTRIM(LTRIM(specialty4)) = '' THEN '' else specialty4 +
		CASE WHEN 
			(specialty5 IS NOT NULL AND RTRIM(LTRIM(specialty5)) <> '') 
			THEN ','
		ELSE '' 
		END 
	END +

	CASE WHEN specialty5 IS NULL OR RTRIM(specialty5) = '' 
		THEN '' 
	ELSE specialty5 
	END

WHERE
RTRIM(ISNULL(specialty1,'')) = '' OR
RTRIM(ISNULL(specialty2,'')) = '' OR
RTRIM(ISNULL(specialty3,'')) = '' OR
RTRIM(ISNULL(specialty4,'')) = '' OR
RTRIM(ISNULL(specialty5,'')) = ''


select * from mdx_physdirectory

Open in new window

result.png
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 GOracle dba
Top Expert 2009

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


if you don't have new information, then why post ?
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 GOracle dba
Top Expert 2009

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

Author

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

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial