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

Question

Post reply

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

December 27, 2021 at 10:22 pm

#3967616

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16167 More actions · Answer 1

why don't you wish to use stuff?

you can use substring - but that will make your code a bit more complex.

on diff note - avoid hardcoding dbnames (use synonyms instead) and remove the nolock - if your company is ok with its pitfalls then use a single statement on start of your code to have the same effect.

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 2

can you please clarify that below

on diff note - avoid hardcoding dbnames (use synonyms instead) and remove the nolock - if your company is ok with its pitfalls then use a single statement on start of your code to have the same effect.

also i need to use string agg function instead

pietlinden SSC Guru Points: 63506 More actions · Answer 3

Look up STRING_AGG() function in Books Online. It was added in SQL Server 2017.

So you're stuck with STUFF. Sorry.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

I want to know why you want to avoid STUFF. What is the reason for such avoidance?

And as several have stated, STRING_AGG() didn't come out until 2017. If you really want to use it, you're going to have to pony up for an upgrade.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)