ahmed elbarbary

ahmed elbarbary

  • 967
  • 1.6k
  • 147.8k

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

Jan 23 2022 6:49 AM

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)

 


Answers (2)