concatenating with a case
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