SQL Server – How to Make a Function Work on Any Table

sql serversql-server-2017

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:

Albuterol Sulfate
Amlodipine Besylate
Aspirin
Benztropine Mesylate
Bisacodyl
Ciprofloxacin
Collagenase
Divalproex Sodium
…

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?

Best Answer

In another answer, @SQLRaptor makes the suggestion of using STRING_AGG. Working with that, it seems like the following should do the trick:

SELECT  AdmitDateTime, RoomID, med.stuffMeds
FROM    AbstractData AD
OUTER APPLY (
    SELECT  STRING_AGG(DataItemID, ', ') WITHIN GROUP (ORDER BY DataItemID ASC) stuffMeds
    FROM    EmrAcctPha_Medication med
    WHERE   AD.VisitID = med.VisitID
) med
Related Question