dcsimg
Results 1 to 4 of 4

Thread: concatenating with a case

  1. #1
    Join Date
    Nov 2002
    Posts
    261

    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

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  3. #3
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    select patientid,
    isnull(allergy1,'')+
    isnull(','+allergy2,'')+
    isnull(','+allergy3,'')+
    isnull(','+allergy4,'') as Allergies
    from symptoms

  4. #4
    Join Date
    Nov 2002
    Posts
    261
    thanks both work

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •