can i use any function or another thing instead of stuff ?

Question

I working on sql server 2014 i need to make value separated by comma
but without using stuff
so how to do that

my sample query

update r  set r.SVHCSubstance=stuff((select ',' + n.substance from ExtractReports.dbo.FinalComplanceDataDelivery rr
        inner join [DocumentCompliance].[SCIP] sc with(nolock) on sc.partid=rr.partid and sc.FeatureName=8508
        INNER JOIN Parts.NormalizedSubCAS n with(nolock) ON n.id = sc.FeatureValue
        where rr.partid=r.partid for xml path('')),1,1,'')
                    ,r.Concertation=stuff((select ',' + sc.Concertation  from ExtractReports.dbo.FinalComplanceDataDelivery rr
        inner join [DocumentCompliance].[SCIP] sc with(nolock) on sc.partid=rr.partid and sc.FeatureName=8508
        INNER JOIN Parts.NormalizedSubCAS n with(nolock) ON n.id = sc.FeatureValue
        where rr.partid=r.partid for xml path('')),1,1,'') 
         from ExtractReports.dbo.FinalComplanceDataDelivery r
         inner join [DocumentCompliance].[SCIP] scc with(nolock) on scc.partid=r.partid

Answer 1

Here is a re-write with string_agg:

update r 
set   r.SVHCSubstance = rr.substance_list,
      r.Concertation = rr.Concertation_list
from  ExtractReports.dbo.FinalComplanceDataDelivery r
join  (select rr.partid, string_agg(n.substance, ',') AS substance_list,
                         string_agg(sc.Concertation, ',') AS Concertation_list
       from  ExtractReports.dbo.FinalComplanceDataDelivery rr
       join  [DocumentCompliance].[SCIP] sc on sc.partid=rr.partid 
                                           and sc.FeatureName=8508
       JOIN  Parts.NormalizedSubCAS n ON n.id = sc.FeatureValue) AS rr ON rr.partid = r.partid

Please note that I don't have your tables and cannot test. This is a fairly mechanical exercise, but I could have slipped.

The shape of the query has changed, because since string_agg is a true aggregate function, we can have more than one in the same subquery, so the above should be more efficient.

But, it does not run on SQL 2014.

Answer 2

stuff has nothing to with comma-separated lists. stuff is a pure string-manipulation function that permits you replace a substring with something else. For instance

SELECT stuff('abcdefghijkl', 4, 3, '12345')

This means, starting a character 4, remove three characters and insert 12345 instead, and the result is abc12345ghijkl.

The actual string concatenation is done with FOR XML PATH:

DECLARE @t TABLE (str nvarchar(20) NOT NULL)
INSERT @t (str) VALUES ('Fridolf'), ('Rutger'), ('Selma')
DECLARE @list nvarchar(MAX)
SELECT @list = (SELECT str + ','
                FROM   @t
                FOR XML PATH(''))
SELECT @list

This returns:

Fridolf,Rutger,Selma,

You get a list, but as you see, there is a trailing comma. Many people wrap the FOR XML PATH mumbo-jumbo in stuff to get rid of that trailing comma. (Or as in your case a leading comma.) I personally find that to be overloaded, and I often remove the comma in a separate step to make the code more readable.

If it's truly stuff you want to get rid of, you can use substring instead. But I suspect that your actual problem is something else, but I don't know what.

Anyway, if you are on SQL 2014 and don't want to use FOR XML PATH for building comma-separated list, the only realistic option is to upgrade to SQL 2017 or later where you can use the much-more straightforward string_agg function instead:

DECLARE @t TABLE (str nvarchar(20) NOT NULL)
INSERT @t (str) VALUES ('Fridolf'), ('Rutger'), ('Selma')
SELECT string_agg(str, ',') FROM @t

And you don't get a trailing comma.

Answer 3

@ahmed salah

Heh... this is the 3 o4 4th forum I've seen you ask this question on and I've asked you on a couple of those why you're trying to avoid STUFF? I can see trying to avoid anything having to do with XML (although it's mostly ok for this type of thing) but not STUFF.

So, why are you trying to avoid STUFF? Knowing the answer to that might help us come up with a good answer for you.