ahmed salah

ahmed salah

  • 1.1k
  • 654
  • 41.6k

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

Sep 14 2022 3:17 PM

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

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

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


Answers (2)