I'm trying to break all the rules of databasing using the stuff function. I want to smush every applicable row into just one, for science, you know?
Problem is, I have to write a new function every time. They look like this:
ALTER function [dbo].[stuffMeds](@VisitID varchar(55)) returns varchar(max) as begin declare @string varchar(max) set @string = (select stuff( ( select distinct top 500 ', ' + DataItemID from EmrAcctPha_Medication med where VisitID = @VisitID order by ', ' + DataItemID desc for xml path ('')),1,2,'') ) return @string end
used in a query like this:
select AdmitDateTime, RoomID, dbo.stuffMeds(VisitID) from AbstractData
It turns a list like this:
into a string like this:
Divalproex Sodium, Collagenase, Ciprofloxacin, Bisacodyl, Benztropine Mesylate, Aspirin, Amlodipine Besylate, Albuterol Sulfate
I can write it as a dynamic stored procedure, but you can't call sp_executesql from a function (or at least, I don't know how).
Question How would you write this function in a way that it could be used on any table?