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

Question

Post reply

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

January 22, 2022 at 9:16 am

Go to Answer

#3976007

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)

Jeff Moden

SSC Guru

Points: 1003851
More actions
January 23, 2022 at 1:31 am
Answer
#3976128

The issue is that they think that they want to stop using the STUFF method (which is really the for XML PATH concatenation) for some reason but won't explain why on this or the other two forums I've see this OP on. I don't understand why they want to mess with something that already working especially since he didn't even know what STRING_AGG() was a week ago.
It would also be nice if he posted the materialized SQL after it's built as well as a sample from the output it creates.
Personally, I'm not touching it until we know why they want to stop using the current working method.
--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 6 posts - 1 through 5 (of 5 total)

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

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 1

You have been posting long enough now to know that you need to provide what you have tried - and where you are having issues.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

January 23, 2022 at 6:47 am

#3976201

ok thank you for support and help

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 3

ahmed_elbarbary.2010 wrote:

ok thank you for support and help

As Jeff stated on the other forum - not the response we were looking for...

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

frederico_fonseca SSCoach Points: 16005 More actions · Answer 4

and unlikely we will get any useful info of the OP - they just ask the question in multiple forums to see if they get any answer that may temporarily address their issue - but always ignoring request for better/complete info.