concatenate with a case

timscronin · Apr 21, 2005

I have a table with 1-4 20 character description fields which need to be concatenated into one value. The data is stored like below

patientid allergy1 allergy2 allergy3 allergy4
100
200 tylenol
300 tylenol bees

The way the data is stored there would never be a allergy3
with an allergy2 being null (the blanks are nulls)

I tried below but it doesn't like the case and concatenation. ANy better ideas

select patientid,allergy_cmt1 +
allergy_cmt2 =
case
when allergy_cmt2 = null then null
else allergy_cmt2 + ','
end
from cms_vw_ptdrugallergies2

mjp483 · Apr 21, 2005

Try this:

select
patientid,
allergy_cmt1 +
ISNULL(allergy_cmt2,'')
from
cms_vw_ptdrugallergies2

ISNULL is a function specifically designed to handle NULLs

Mike

tc3596 · Apr 21, 2005

try this...

select
patientid,
Case When isnull(rtrim(allergy_cmt1), '') = '' Then 'No Data'
Else allergy_cmt1 + Case When isnull(rtrim(allergy_cmt2), '') = '' Then ''
Else ', ' + allergy_cmt2 + Case When isnull(rtrim(allergy_cmt3), '') = '' Then ''
Else ', ' + allergy_cmt3 + Case When isnull(rtrim(allergy_cmt4), '') = '' Then ''
Else ', ' + allergy_cmt4
End
End
End
End
from
cms_vw_ptdrugallergies2

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

concatenate with a case

timscronin

MIS

mjp483

Programmer

tc3596

Technical User

Similar threads

Part and Inventory Search

Sponsor