SQLTeam.com | Weblogs | Forums

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

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)

What have you tried so far - and where are you having issues?

1 Like

Just like I asked you over on SQL Server Central, why are you hell bent on replacing STUFF() (and what you really mean is the concatenation being done by for FOR XML PATH)? What is the issue with that?

ok thanks

That's not quite the answer I was looking for. The question is WHY do you want to get rid of "Stuff"?

1 Like

https://docs.microsoft.com/en-us/answers/questions/705694/how-to-replace-stuff-with-string-agg-on-sql-server.html

Even other forums are noticing you are not learning and just posting same question in many other forums so that others do your work.

1 Like

thanks