Ok I have a stored procedure in which I am pulling information from to different servers and combining it with a series of joins. My problem is that I am trying to get the names of everyone in the operating room, but I need to combine them into one field. Right now I get all the names, but each as its own record, so I have the same information like 7 times except that the name changes.
Here is the code:
--alter Procedure InfectionStuff
--(@StartDate Datetime, @EndDate DateTime)
--AS
Select
s.proc_date,
s.proc_type as [Procedure Type],
s.urgency as [Urgency],
s.age_at_surgery,
p.sex as [Sex],
Case When s.height >0 and s.weight >0 then round(SQRT(((s.[Height]*s.[Weight])/3600)),2) else null end AS [BSA],
case when s.height >0 and s.weight >0 then (s.[Weight]/((s.[Height]/100)*(s.[Height]/100))) else null end AS [BMI],
s.nyha_class as [NYHA Class],
s.diabetes,
s.copd,
s.ejection_fraction as [EF],
s.pre_op_hct,
s.primary_surgeon,
s.anesthesia,
s.primary_perfusionist,
staff.[name],
st.total_or_time,
pu.pump_time,
Min(cardiac_surg.dbo.PumpStat.[Hct]) as [Min Pump Hct],
AVG(cardiac_surg.dbo.pumpstat.glucose) as [AVG Intra-Op Glucose],
MAX(cardiac_surg.dbo.pumpstat.glucose) as [MAX Intra-Op Glucose],
st.ventilator_time,
st.icu_los,
(st.icu_los + st.post_op_los) as [LOS],
b.homologous_rbc,
CAST(AVG( b.[Platelet_Pheresis])+(AVG( b.[Platelet_Concentrate]))+(AVG( b.[Cryoprecipitate]))+(AVG( b.[FFP]))+(AVG( b.[Homologous_RBC])) AS REAL) as [Avg Donor Exp],
s.pod_0_hct as [Hct POD #0],
s.dc_status as [Discharge Status]
From Cardiac_surg.dbo.surgproc s left join cardiac_surg.dbo.pt_ident p
on s.social_security_number = p.social_security_number
left join cardiac_surg.dbo.surgery_times st
on s.surg_proc_number = st.surg_proc_number
left join cardiac_surg.dbo.pumptime pu
on s.surg_proc_number = pu.surg_proc_number
left join cardiac_surg.dbo.pumpstat
on s.surg_proc_number = cardiac_surg.dbo.pumpstat.surg_proc_number
left join cardiac_surg.dbo.blood b
on s.surg_proc_number = b.surg_proc_number
left join [ordbserv1\orsos92sql2k].orsos.orsos.facility_patient fp
on p.[6 digit hospital number] = fp.fmrn
left join [ordbserv1\orsos92sql2k].orsos.orsos.pre_case pc
on fp.medical_record_no = pc.medical_record_no
left join [ordbserv1\orsos92sql2k].orsos.orsos.post_resource pr
on pc.case_no = pr.case_no
left join [ordbserv1\orsos92sql2k].orsos.orsos.staff staff
on pr.resource_id = staff.staff_id
where
--s.proc_date >= @StartDate and s.proc_date <= @EndDate
s.proc_date >= '7/1/2004' --and s.proc_date <= @EndDate
and
pr.procedure_id = '901001'
group by
s.proc_type, s.urgency, s.age_at_surgery, p.sex, s.height, s.weight, s.nyha_class, s.diabetes,
s.copd, s.ejection_fraction, s.pre_op_hct, s.primary_surgeon, s.anesthesia, s.primary_perfusionist,
st.total_or_time, pu.pump_time, st.ventilator_time, st.icu_los, st.post_op_los, b.homologous_rbc,
s.pod_0_hct, s.dc_status, staff.[name], s.proc_date
GO
My output looks like this:
A B C Name D E
1 2 3 Sam 4 5
1 2 3 Pat 4 5
1 2 3 Fred 4 5
And I want it to look like this:
A B C Name D E
1 2 3 Sam, Pat, Fred 4 5
Suggestions greatly appreciated!
Thanks,
Jeremy
Here is the code:
--alter Procedure InfectionStuff
--(@StartDate Datetime, @EndDate DateTime)
--AS
Select
s.proc_date,
s.proc_type as [Procedure Type],
s.urgency as [Urgency],
s.age_at_surgery,
p.sex as [Sex],
Case When s.height >0 and s.weight >0 then round(SQRT(((s.[Height]*s.[Weight])/3600)),2) else null end AS [BSA],
case when s.height >0 and s.weight >0 then (s.[Weight]/((s.[Height]/100)*(s.[Height]/100))) else null end AS [BMI],
s.nyha_class as [NYHA Class],
s.diabetes,
s.copd,
s.ejection_fraction as [EF],
s.pre_op_hct,
s.primary_surgeon,
s.anesthesia,
s.primary_perfusionist,
staff.[name],
st.total_or_time,
pu.pump_time,
Min(cardiac_surg.dbo.PumpStat.[Hct]) as [Min Pump Hct],
AVG(cardiac_surg.dbo.pumpstat.glucose) as [AVG Intra-Op Glucose],
MAX(cardiac_surg.dbo.pumpstat.glucose) as [MAX Intra-Op Glucose],
st.ventilator_time,
st.icu_los,
(st.icu_los + st.post_op_los) as [LOS],
b.homologous_rbc,
CAST(AVG( b.[Platelet_Pheresis])+(AVG( b.[Platelet_Concentrate]))+(AVG( b.[Cryoprecipitate]))+(AVG( b.[FFP]))+(AVG( b.[Homologous_RBC])) AS REAL) as [Avg Donor Exp],
s.pod_0_hct as [Hct POD #0],
s.dc_status as [Discharge Status]
From Cardiac_surg.dbo.surgproc s left join cardiac_surg.dbo.pt_ident p
on s.social_security_number = p.social_security_number
left join cardiac_surg.dbo.surgery_times st
on s.surg_proc_number = st.surg_proc_number
left join cardiac_surg.dbo.pumptime pu
on s.surg_proc_number = pu.surg_proc_number
left join cardiac_surg.dbo.pumpstat
on s.surg_proc_number = cardiac_surg.dbo.pumpstat.surg_proc_number
left join cardiac_surg.dbo.blood b
on s.surg_proc_number = b.surg_proc_number
left join [ordbserv1\orsos92sql2k].orsos.orsos.facility_patient fp
on p.[6 digit hospital number] = fp.fmrn
left join [ordbserv1\orsos92sql2k].orsos.orsos.pre_case pc
on fp.medical_record_no = pc.medical_record_no
left join [ordbserv1\orsos92sql2k].orsos.orsos.post_resource pr
on pc.case_no = pr.case_no
left join [ordbserv1\orsos92sql2k].orsos.orsos.staff staff
on pr.resource_id = staff.staff_id
where
--s.proc_date >= @StartDate and s.proc_date <= @EndDate
s.proc_date >= '7/1/2004' --and s.proc_date <= @EndDate
and
pr.procedure_id = '901001'
group by
s.proc_type, s.urgency, s.age_at_surgery, p.sex, s.height, s.weight, s.nyha_class, s.diabetes,
s.copd, s.ejection_fraction, s.pre_op_hct, s.primary_surgeon, s.anesthesia, s.primary_perfusionist,
st.total_or_time, pu.pump_time, st.ventilator_time, st.icu_los, st.post_op_los, b.homologous_rbc,
s.pod_0_hct, s.dc_status, staff.[name], s.proc_date
GO
My output looks like this:
A B C Name D E
1 2 3 Sam 4 5
1 2 3 Pat 4 5
1 2 3 Fred 4 5
And I want it to look like this:
A B C Name D E
1 2 3 Sam, Pat, Fred 4 5
Suggestions greatly appreciated!
Thanks,
Jeremy