SQLTeam.com | Weblogs | Forums

How to display size feature on final result where partc and partx not have same feature?

I work on SQL server 2012 I face issue i can't display feature size on final result query

this happen when replace temp table partc and part x not have

value to same feature as size feature

but if part c and partx have same feature no problem it is ok

DROP TABLE IF EXISTS #replace
 DROP TABLE IF EXISTS #FeatureNameandValues
 DROP TABLE IF EXISTS #Temp
 DROP TABLE IF EXISTS #Temp1
 DROP TABLE IF EXISTS #Temp2
    
 create table #replace
  (
  PartIdc int,
  PartIdx int,
  )
  insert into #replace(PartIdc,PartIdx)
  values
  (1211,1300),
  (2000,2200),
  (3000,3100),
  (4150,4200)
        
  create table #FeatureNameandValues
  (
  PartId int,
  [FeatureName] nvarchar(20),
  [FeatureValue] int
  )
  insert into #FeatureNameandValues(PartId,[FeatureName],[FeatureValue])
  values
  (1211,'Weight',5),
  (2000,'Tall',20),
  (3000,'Weight',70),
  (4150,'Tall',190),
  (1211,'Tall',80),
  (1300,'Weight',10),
  (3100,'Size',150),
  (4200,'Tall',130),
  (1300,'Tall',20)
    
    
    
    
    
  SELECT a.[FeatureName] [FeatureName], CASE WHEN a.PartId = b.PartIdc THEN 1 WHEN a.PartId=b.PartIdx THEN 2 END PartOrder, b.PartIdc PartC,b.PartIdx PartX,  a.[FeatureValue] [FeatureValue]
  INTO #Temp
  FROM #FeatureNameandValues a
  JOIN #replace b ON  a.PartId = b.PartIdc OR a.PartId = b.PartIdx
    
  -- Find out different values 
  -- If value belongs to PartC, then order = 1; PartX, order = 2 
  -- So that the feature value for c will be the former one
  SELECT a.[FeatureName] [FeatureName], a.PartOrder, a.PartC PartC, a.PartX PartX, a.[FeatureValue] [FeatureValue]
  INTO #Temp1
  FROM #Temp a
  JOIN #Temp b ON a.FeatureName=b.FeatureName AND a.PartC=b.PartC AND a.PartX=b.PartX AND a.[FeatureValue] <> b.[FeatureValue]
    
  -- Display the result for different values
  SELECT * FROM #Temp1 
  ORDER BY  PartC,PartX,[FeatureName],PartOrder
    
    
  -- Concatenate the values for each group
 SELECT T1.[FeatureName], T1.PartC, T1.PartX,
         STUFF(  
         (  
         SELECT '-' + CAST(T2.[FeatureValue] AS VARCHAR(MAX))
         FROM #Temp1 T2  
         WHERE T1.[FeatureName] = T2.[FeatureName] AND T1.PartC = T2.PartC AND T1.PartX = T2.PartX
         FOR XML PATH ('')  
         ),1,1,'') [Difference]
 INTO #Temp2 
 FROM #Temp1 T1  
 GROUP BY  T1.PartC,T1.PartX,T1.[FeatureName]
    
 SELECT * FROM #Temp2
    
 -- Out one row
 SELECT STUFF(
 (SELECT ' | ' + [FeatureName] + '( '+ [Difference] + ' )' FROM #Temp2 FOR XML PATH('')),
 1,2,'') AS [Result]

final result expected is :

Tall (80-20) | Weight(5-10) | size(NULL-150) | Tall(190-130)

wrong result is

Tall (80-20) | Weight(5-10) | Tall(190-130)