Post reply

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

  • 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

  • December 27, 2021 at 10:28 pm

    #3967617

    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.

  • December 27, 2021 at 11:00 pm

    #3967622

    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

  • December 28, 2021 at 2:29 am

    #3967653

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

    So you're stuck with STUFF. Sorry.

     

  • December 28, 2021 at 6:28 am

    #3967712

    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)
    Intro to Tally Tables and Functions

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

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