Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to change STRING_AGG to stuff xml to split feature value by pip?

Status
Not open for further replies.

ahmedsa2018

Programmer
Apr 25, 2018
67
EG
I work on sql server 2019 . i can't write query below with stuff for xml .

so how to change STRING_AGG to stuff xml ?

query below take too much time so i need to try

with stuff for xml to reduce time cost .

query i try it

SQL:
select
   a.RecomendationId,
   cast(STRING_AGG(cast(f1.FeatureValue as varchar(300)) ,'|')  WITHIN GROUP (ORDER BY f1.FeatureId ASC) as varchar(300)) AS DiffFeatures
 into ExtractReports.dbo.TechnologyOriginalFeaturesEqual
 from extractreports.dbo.partsrecomendationActive a 
 inner join ExtractReports.dbo.TechnologyPlPartsFeaturValuesOrg f1  on f1.partid = a.OrignalPartId
 inner join [Technology].Receipe Ft  on ft.featureid = f1.featureid and ft.operatorid = 1
 group by a.RecomendationId

ddl tables structures

SQL:
 create table ExtractReports.dbo.TechnologyPlPartsFeaturValuesOrg
       (
       ID int identity(1,1),
       PartId int,
       FeatureID int,
       FeatureName varchar(200),
       FeatureValue varchar(200)
       )
    
 create table extractreports.dbo.partsrecomendationActive
 (
 RecomendationId int identity(1,1),
 OrignalPartId int
    
 )
    
 CREATE TABLE [Technology].[Receipe](
     [ReceipeID] [int] IDENTITY(1,1) NOT NULL,
     [FeatureID] [int] NULL,
     [OperatorID] [int] NULL,
 PRIMARY KEY CLUSTERED 
 (
     [ReceipeID] ASC
 ))
expected result
as image attached
 
 https://files.engineering.com/getfile.aspx?folder=12ec01df-22a6-4ee5-b4eb-74b0461f2676&file=SAMPLEIMAGE.png
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top