How to Update field updatedStuffDiff when i have different values for parts exist on temp table?

Transact-SQL
, ,
1

I work on sql server 2012 I face issue I can't update column name updatedStuffDiff

with different feature value for every feature based on partid

so when i have two part have same feature but different values

then display it as result on column updatedStuffDiff

based on partidc and partidx on table #temp

so result i needed is featurename(partidc feature value - partidx featurevalue)

and if no value for feature value for partidc or partidx

then featurename(partidc feature value - NULL) OR (NULL- partidx featurevalue)

SO first i will get partidc and partidx from table temp
then get values related to it from #Featuresvalue and compare to it
and then update result to column updatedStuffDiff

create table #temp
 (
 PartIdc int,
 PartIdx int
 )
 insert into #temp(PartIdc,PartIdx)
 values
 (555,1010),
 (591,888)
 create table #Featuresvalue
 (
 PartId int,
 FeatureName nvarchar(50),
 FeatureValue  nvarchar(50),
 updatedStuffDiff nvarchar(500)
 )
 insert into #Featuresvalue(PartId,FeatureName,FeatureValue)
 values
 (555,'Temperature','5c'),
 (555,'resistance','10c'),
 (1010,'Temperature','20c'),
 (1010,'resistance','30c'),
 (1010,'cold','40c'),
 (1010,'air','7c'),
    
 (888,'Temperature','51c'),
 (888,'resistance','15c'),
 (591,'Temperature','25c'),
 (591,'resistance','40c'),
 (591,'cold','70c'),
 (591,'air','8c'),
 (591,'stress','17c'),
 (591,'pressure','70c')

Expected Result

when update column updatedStuffDiff i will have result two rows :

 Temperature(5c-20c)resistance(10c-30c)cold(NULL-40c) air(NULL-7c)
 Temperature(25c-51c)resistance(40c-15c)cold(70c-NULL) air(8c-NULL)stress(17C-NULL)pressure(70c-NULL)
2

What have you tried so far?

3

ok thanks
i solved it and this is my solution

;with cte as (
 select * from #temp a 
 inner join #Featuresvalue b on a.PartIdc=b.PartId)
 ,cte1 as (
 select * from #temp a 
 inner join #Featuresvalue b on a.PartIdx=b.PartId)
 ,cte2 as (
 select isnull(a.PartIdc,b.PartIdc) PartIdc,isnull(b.FeatureName,a.FeatureName) FeatureName
 ,a.FeatureValue,a.updatedStuffDiff,b.FeatureValue FeatureValue2
 from cte a
 full join cte1 b 
 on a.PartIdc=b.PartIdc and a.FeatureName=b.FeatureName)
 select distinct stuff(
 (select  concat( ' ', FeatureName, ' (', isnull(cast(FeatureValue as varchar(max)), 'NULL')
 , '-', isnull(cast(FeatureValue2 as varchar(max)), 'NULL'), ')')
 from cte2 b
 where b.PartIdc=a.PartIdc
 for xml path('')),
 1, 1, '')
 from cte2 a