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

Question

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

as example below :

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) 

Answer 1

Hi @ahmed salah ,

  -- 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]  

Replace the above code with:

      ;WITH cte  
      as(SELECT *,ROW_NUMBER() OVER(PARTITION BY [FeatureName], [FeatureValue] ORDER BY [FeatureValue]) rr FROM #Temp)  
        
      SELECT [FeatureName],PartOrder,PartC,PartX,[FeatureValue]  
      INTO #Temp1  
      FROM cte  
      WHERE rr<2  

Final output:

      Result  
      Tall( 20-80 ) | Weight( 5-10 ) | Size( 150 ) | Weight( 70 ) | Tall( 130-190 )  

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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

sorry final result i need is

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

i need when have feature but not have value to it to display as Null

tall is null for part x because partx(3100) not have value for feature tall

size is Null for partc (2000) because not have value for feature size for partc

 create table #replace
  (
  PartIdc int,
  PartIdx int,
  )
  insert into #replace(PartIdc,PartIdx)
  values
  (1211,1300),  Weight(5-10)
  (2000,2200),  Tall (20-NULL)
  (3000,3100), size(NULL-150)
  (4150,4200) Tall(190-130)

Answer 3

  -- 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], cast(a.PartOrder as varchar) PartOrder,cast(a.PartC as varchar) PartC,cast(a.PartX as varchar) PartX, cast( a.[FeatureValue] as varchar) [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]
  union all
  select 'Size','2','3000','3100','NULL'
  union all
  select [FeatureName], cast(PartOrder as varchar) ,cast(PartC as varchar),cast(PartX as varchar), cast( [FeatureValue] as varchar) 
  from #Temp where [FeatureName]='size'

  -- Display the result for different values

Final result:

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