concatenating with a case

[timc]

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

[MAK]

create table symptoms (patientid int, allergy1 varchar(20),allergy2 varchar(20),allergy3 varchar(20),allergy4 varchar(20))
insert into symptoms (patientid) select 100
insert into symptoms (patientid,allergy1) select 200, 'tylenol'
insert into symptoms (patientid,allergy1,allergy2) select 300, 'tylenol', 'bees'
insert into symptoms (patientid,allergy1,allergy2,allergy3) select 400, 'tylenol', 'bees','pencillin'
insert into symptoms (patientid,allergy1,allergy2,allergy3,allergy4) select 500, 'tylenol', 'bees','pencillin','alcohol'



select patientid, Allergies=
case
when Allergy1 is NULL then NULL
when Allergy2 is null then allergy1
when Allergy3 is null then allergy1+','+allergy2
when Allergy4 is null then allergy1+','+allergy2+','+allergy3
else allergy1+','+allergy2+','+allergy3+','+allergy4 end
from symptoms

[Claire]

select patientid,
isnull(allergy1,'')+
isnull(','+allergy2,'')+
isnull(','+allergy3,'')+
isnull(','+allergy4,'') as Allergies
from symptoms

[timc]

thanks both work