4

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?

3

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
  • huh, i've never seen something like that. I'll give it a try. – James Jun 1 '18 at 19:22
2

You have a built in function in SQL Server for this called STRING_AGG. Check out the documentation.

  • my group by is going to be humongous. I'll play with string_agg for a bit. Maybe i'll use a bunch of CTE's to keep the grouping simpler. – James Jun 1 '18 at 14:57
  • 2
    @James it is very likely that the STRING_AGG will perform way better than the XML PATH approach you used originally. Let us know what you find. – SQLRaptor Jun 1 '18 at 15:22
1

If we're breaking the rules and doing this for science...how about.

Create a View that Unions together the required columns from all the tables you are interested in and have a column that includes the source table name i.e. EmrAcctPha_Medication.

Then pass in the tablename to the function as a variable, and filter on @tablename & @visited.

ALTER function [dbo].[stuffMeds](@VisitID varchar(55),@tablename varchar(255))
returns varchar(max)

as
begin
declare @string varchar(max)

set @string = (select stuff( (
select  distinct top 500 ', ' + DataItemID  from 
dbo.myreallybigview med
where 
VisitID = @VisitID
AND
TableName = @tablename
order by ', ' +  DataItemID desc
for xml path ('')),1,2,'')
 )

return @string

end
  • what could possibly go wrong? I love it. – James Jun 1 '18 at 16:12
  • @James You are kidding, right? – SQLRaptor Jun 1 '18 at 18:12
  • lol yes. Lots could go wrong. I won't implement it. I still like it. – James Jun 1 '18 at 18:13
  • @James it seems dangerous is the middle name of this hospital, keeping medications in different tables for each ward/department will eventually kill a patient who moves between wards. – pacreely Jun 1 '18 at 18:17
  • @pacreely where did you get the idea that we do that? – James Jun 1 '18 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.