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

Question

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)

Answer 1

Hi,@ahmed salah
Please check this:

SELECT PartID,Code,CodeTypeID,RevisionID,ZPLID,Count(1) as ConCount,  
       STRING_AGG(ColumnName,'$')WITHIN GROUP (ORDER BY G.ZfeatureKey) AS FeatureName,  
       STRING_AGG(FeatureValue,'$')WITHIN GROUP (ORDER BY G.ZfeatureKey) AS FeatureValue  
FROM core_datadefinition_Detailes C  
     JOIN gen G ON G.ZfeatureKey=C.ColumnNumber  
     JOIN PartAttributes P ON P.ZfeatureKey=G.ZfeatureKey  
GROUP BY PartID,Code,CodeTypeID,RevisionID,ZPLID   

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

When you ask a question and present some info then please focus on the issue. It seems like in your case you want to use STRING_AGG function instead of using using FOR AML (by the way, stuff is not what aggregate the data but the FOR XML part). The idea is to simplifies the scenario and present a full demo which focus on the issue.

In your query you are using dynamic query. How this is relevant to the issue?!?

(1) PRINT the query instead of Execute it in order to get the query which you ask about

(2) Once I print the query I see that you have INSERT... SELECT, but only the SELECT part is relevant to the question, so why do you need to present us complex query instead focus on the SELECT?

(3) In the SELECT query you have multiple columns which are not related to the question. For example, the SELECT of this part PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount, is not relevant to question since you only want to understand how to change the last two columns which uses FOR XML to use STRING_AGG, so we can simplifies the query for the sake of the question to select only the last two relevant columns.

In fact, lets focus on one column first, since the solution is the same.

Can you understand that we could focus on the following query for the sake of the discussion? Will a solution for the following query solve your needs?

SELECT 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

(4) In the above query, the sub-query inside the FROM is a simple SELECT query. It can be replaced with any other query. This is not relevant to your question. right?

Therefore, for the sake of the discussion regarding your question you could presented us a simpler query like:

SELECT stuff(
    ( SELECT  '$' + CAST( CP.ColumnName AS VARCHAR(500)) AS [text()]
    FROM CP
    where CP.codeTypeId=Co.codeTypeId and CP.Code=Co.Code
    ORDER BY CP.ZfeatureKey
    FOR XML PATH('')
    ) 
    , 1,  1, ''
) as FeatureName

The original query uses two tables: ExtractReports.dbo.TPartAttributes with the alias PM and ExtractReports.dbo.TCondition with the alias Co

The use of Co is only in the WHERE part, and the this part is not relevant to the question, so let's remove it to focus on simpler query which uses one simple table.

In your case, after I clean the not-relevant information, then basically you have two queries which looks like this:

SELECT stuff(
 (SELECT  '$' + CP.ColumnName
 FROM CP
 ORDER BY CP.ZfeatureKey
 FOR XML PATH('')
 ) 
 , 1,  1, ''
) as FeatureName
GO

You should ALWAYS provide relevant table(s) with some data for the sake of the discussion.

For such question using the above simplified query we need a table CP (no need for all the real original table which sued for JOIN query but just simplified it to simple table) with the columns ColumnName, ZfeatureKey

So this is the simple DDL+DML (queries to create the table and insert sample data) which we can present the question and discuss the answer on

DROP TABLE IF EXISTS CP;
GO
CREATE TABLE CP(ColumnName NVARCHAR(100), ZfeatureKey int)
GO
INSERT CP(ColumnName, ZfeatureKey) VALUES ('a',1),('b',3),('c',2),('d',6),('e',4)
GO

And now we can present the solution for this simple case:

-- Using FOR XML
SELECT stuff(
 (SELECT  '$' + CP.ColumnName
 FROM CP
 ORDER BY CP.ZfeatureKey
 FOR XML PATH('')
 ) 
 , 1,  1, ''
) as FeatureName
GO

-- Using STRING_AGG
SELECT STRING_AGG(ColumnName,'$') WITHIN GROUP (ORDER BY ZfeatureKey)
FROM CP
GO

Try to implement this simple case in your real case

Basically you have two places where you use FOR XML which you can now replace with using STRING_AGG function.

If you do not succeed the you will need to provide us the tools to preproduce the real scenario! meaning you need to provide queries to create any relevant table which is used and insert some sample data to the table for the sake of the discussion.

Answer 3

The purpose of stuff does is to remove and add charcaters to a string:

SELECT stuff('ThisIsMyString, , 6, 2, 'Your')

string_agg is something completely different. Oh, so you mean FOR XML? Well, I gave you an example like two-three weeks ago. Why don't you go back an revisit your old threads?

(My aim when answering questions in forums is not only to help for the moment, but to help people to take the next step for the future, so they don't have to ask about the same thing again. Therefore, it is not very rewarding when someone comes back and ask the same thing again. To be able to switch from FOR XML to string_agg_, you will need to understand your code. )

Answer 4

You can apply on string_agg on sample below

check sample below :

you can check

IF OBJECT_ID('[dbo].[gen]') IS NOT NULL  
 DROP TABLE [dbo].[gen]      
 IF OBJECT_ID('[dbo].[PartAttributes]') IS NOT NULL  
 DROP TABLE [dbo].[PartAttributes]      
 IF OBJECT_ID('dbo.core_datadefinition_Detailes') IS NOT NULL  
 DROP TABLE core_datadefinition_Detailes  
      
      
      
      
 CREATE TABLE core_datadefinition_Detailes(  
     [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,  
     [ColumnName] [nvarchar](500) NOT NULL,  
     [ColumnNumber] [int] NOT NULL,  
          
  CONSTRAINT [PK_Core_DataDefinition_Details] PRIMARY KEY CLUSTERED   
 (  
     [ID] ASC  
 )  
 )  
 insert into core_datadefinition_Detailes([ColumnNumber],[ColumnName])  
 values  
 (202503,'Product Shape Type'),  
 (1501170111,'Type'),  
 (202504,'Package Family')  
      
      
      
      
      
      
      
 CREATE TABLE [dbo].[gen](  
     [TradeCodeControlID] [int] IDENTITY(1,1) NOT NULL,  
     [CodeTypeID] [int] NULL,  
     [RevisionID] [bigint] NULL,  
     [Code] [varchar](20) NULL,  
     [ZPLID] [int] NULL,  
     [ZfeatureKey] [bigint] NULL,  
      
 ) ON [PRIMARY]  
      
      
 GO  
 SET IDENTITY_INSERT [dbo].[gen] ON   
 INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],  [Code], [ZPLID], [ZfeatureKey]) VALUES (7565,  849774, 307683692, N'8541100050', 4239, 202503)  
 INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],  [Code], [ZPLID], [ZfeatureKey]) VALUES (7566,  849774, 307683692, N'8541100050', 4239, 202504)  
 INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],   [Code], [ZPLID], [ZfeatureKey]) VALUES (7567,  849774, 307683692, N'8541100050', 4239, 1501170111)  
      
      
 SET IDENTITY_INSERT [dbo].[gen] OFF  
      
 CREATE TABLE [dbo].[PartAttributes](  
     [PartID] [int] NOT NULL,  
     [ZfeatureKey] [bigint] NULL,  
     [AcceptedValuesOption_Value] [float] NULL,  
     [FeatureValue] [nvarchar](500) NOT NULL  
 ) ON [PRIMARY]  
      
 GO  
 INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey],  [FeatureValue]) VALUES (413989, 202503, N'Discrete')  
 INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey],  [FeatureValue]) VALUES (413989, 1501170111, N'Zener')  
 INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey],  [FeatureValue]) VALUES (413989, 202504, N'SOT')  
      
      
      
      
              
      
 SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount,  
                 stuff(( SELECT  '$' + CAST( CP.ColumnName AS VARCHAR(300)) AS [text()]  
                     FROM(SELECT distinct d.ColumnName,C.codeTypeId,C.Code,C.ZfeatureKey from gen C   
                     inner join core_datadefinitiondetails d with(nolock) on C.ZfeatureKey=d.columnnumber  
                     INNER JOIN PartAttributes P on P.partid=PM.partid)CP  
                     where CP.codeTypeId=Co.codeTypeId and CP.Code=Co.Code  
                     ORDER BY CP.ZfeatureKey  
                      
                     FOR XML PATH(''), TYPE  
                      ).value('.', 'NVARCHAR(MAX)')   
                         , 1,  1, '') as FeatureName,  
                         stuff(( SELECT  '$' + CAST( CP2.FeatureValue AS VARCHAR(300)) AS [text()]  
                     FROM(SELECT distinct P.FeatureValue,C2.codeTypeId,C2.Code,C2.ZfeatureKey FROM gen C2  
                     INNER JOIN PartAttributes P on C2.ZfeatureKey=P.ZfeatureKey)CP2  
                     where CP2.codeTypeId=Co.codeTypeId and CP2.Code=Co.Code  
                     ORDER BY CP2.ZfeatureKey  
                     FOR XML PATH(''), TYPE  
                      ).value('.', 'NVARCHAR(MAX)')   
                         , 1,  1, '') as FeatureValue  
                 FROM   
                 PartAttributes PM   
                 INNER JOIN    gen Co ON Co.ZfeatureKey = PM.ZfeatureKey Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID    
      
                 Expected result  

I make stuff for feature name and feature value based on partid and codetype and code

Answer 5

Here is a query that demonstrates the use of string_agg

SELECT object_id, string_agg(name, ', ') WITHIN GROUP (ORDER BY name)
FROM   sys.columns
GROUP  BY object_id
ORDER  BY object_id

For reference, here is the same query implemented with FOR XML PATH:

SELECT o.name, (SELECT c.name + ','
                FROM   sys.columns c
                WHERE  o.object_id = c.object_id
                ORDER  BY c.name
                FOR XML PATH(''))
FROM   sys.objects o
ORDER  BY o.name

Here I am using some of the system views in SQL Server, since they are always available. Study these patterns and apply to your own tables. Then you can try to rewrite your queries.

I am here to help, if you want to learn. And if you don't want to learn - well, that I not that interested in helping for free.