Post reply

How to replace stuff with string agg on sql server 2017 ?

  • January 22, 2022 at 9:16 am

    #3976008

    i work on sql server 2017 i need to replace stuff with sting agg string_agg

    so how to do that please

    SET @Sql= CONCAT('INSERT INTO ExtractReports.dbo.TAllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount,FeatureName,FeatureValue)',' SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount,
                 stuff(( SELECT  ''$'' + CAST( CP.ColumnName AS VARCHAR(500)) AS [text()]
     FROM(SELECT distinct d.ColumnName,C.codeTypeId,C.Code,C.ZfeatureKey from ExtractReports.dbo.TCondition C with(nolock) 
                     inner join core_datadefinitiondetails d with(nolock) on C.ZfeatureKey=d.columnnumber
                     INNER JOIN ExtractReports.dbo.TPartAttributes P with(nolock) on P.partid=PM.partid)CP
                     where CP.codeTypeId=Co.codeTypeId and CP.Code=Co.Code
                     ORDER BY CP.ZfeatureKey
                    
     FOR XML PATH('''')) 
         , 1,  1, '''') as FeatureName,
                         stuff(( SELECT  ''$'' + CAST( CP2.Name AS VARCHAR(500)) AS [text()]
     FROM(SELECT distinct P.partId,P.Name,C2.codeTypeId,C2.Code,C2.ZfeatureKey FROM ExtractReports.dbo.TCondition C2 with(nolock)
                     INNER JOIN ExtractReports.dbo.TPartAttributes P with(nolock) on C2.ZfeatureKey=P.ZfeatureKey)CP2
                     where CP2.codeTypeId=Co.codeTypeId and CP2.Code=Co.Code and CP2.PartId=PM.partid
                     ORDER BY CP2.ZfeatureKey
     FOR XML PATH('''')) 
         , 1,  1, '''') as FeatureValue
                 FROM 
                 ExtractReports.dbo.TPartAttributes PM with(nolock) 
                 INNER JOIN    ExtractReports.dbo.TCondition Co with(nolock) ON Co.ZfeatureKey = PM.ZfeatureKey ',                
                 'Where (1=1 and  ',@Con ,  @ConStr,' ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID ' ,
                 ' Having Count(1)>= ',(SELECT COUNT(1) FROM ExtractReports.dbo.TCondition with(nolock)))
            
             EXEC (@SQL)

  • January 23, 2022 at 10:10 am

    #3976224

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • January 23, 2022 at 10:17 am

    #3976226

    Duplicate post

  • January 23, 2022 at 5:35 pm

    #3976278

    And still no answer as the WHY this needs to be done.

    --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)

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

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