0

I'm trying to get this

PatID   FacID   RxNo        RejectCd
121403  463     57074566    R9 
121403  463     57074566    76
121403  463     57074566    88
121403  463     57074566    8C

To look like this 

PatID   FacID   RxNo        RejectCd
121403  463     57074566    R9,76,88,8c

Tried using a pivot or doing it in stages but I'm stuck. Can't pivot because I don't have a field for the column names. Also don't know how many Reject Codes will actually exist till it runs but we can ignore that part for now. I think this will be really challenging for any SQL nerds out there. Thank u!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2001292
  • 11
  • 2

1 Answers1

1

There are a few ways to concatenate row values, but one of the simplest and most common is using for xml path(''). 

select
    PatId
  , FacId
  , RxNo
  , RejectCd = stuff(
      (
      select ',' + i.RejectCd
        from t as i
        where i.PatId = t.PatId
          and i.FacId = tFacId
          and i.RxNo  = t.RxNo
      for xml path (''), type).value('.','varchar(max)')
      ,1,1,'')
from t
group by 
    PatId
  , FacId
  , RxNo

in sql server vNext you will be able to use string_agg():

select 
    PatId
  , FacId
  , RxNo
  , RejectCd = string_agg (RejectCd, ',') 
      /* optional sort */ within group (order by RejectCd asc) 
from t 
group by 
    PatId
  , FacId
  , RxNo
SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • Don't have the string_agg function so i used the first solution and it worked perfectly! Could not be more grateful! thank u! thank u! thank u! – user2001292 Feb 24 '17 at 16:57