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

• • Reply
  ahmedbarbary

  Member

  41 Points

  376 Posts

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

  Mar 01, 2021 10:50 PM|ahmedbarbary|LINK

  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)

• • Reply
  Sean Fang

  Contributor

  3020 Points

  891 Posts

  Re: How to Update field updatedStuffDiff when i have different values for parts exist on temp tab...

  Mar 02, 2021 07:37 AM|Sean Fang|LINK

  Hi ahmedbarbary,

   

  I don't think doing such a complex concatenation in SQL server is a good idea. You should make those data processing in other places, for example, if you have a web application, you should do this in bussiness layer.

   

  Anyway, if you insist on dealing with the data in sql server, in your case, you will need to use dynamic SQL since the "Featuresvalue" are various with respect to the different parts.

  Brief steps explanation:

  • Fetch all feature names from the table #Featuresvalue to get a PIVOT table for all values of each pair
  • Construct a @select string to dynamically concatenate the final result
  • Use dynamic SQL to execute and get the result
  • Then you are able to update the table with the result (still need to do this in dynamic SQL) 

  Dynamic SQL Statement:

  DROP TABLE  IF EXISTS #temp
  DROP TABLE  IF EXISTS #Featuresvalue
  DROP TABLE  IF EXISTS #temptable
  
  create table #temp
   (
   PartIdc int,
   PartIdx int,
   updatedStuffDiff nvarchar(500)
   )
   insert into #temp(PartIdc,PartIdx)
   values
   (555,1010),
   (591,888)
   create table #Featuresvalue
   (
   PartId int,
   FeatureName nvarchar(50),
   FeatureValue  nvarchar(50)
   
   )
   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')
  
  
  
  
  DECLARE @cols AS NVARCHAR(MAX),
      @select AS NVARCHAR(MAX),
      @query  AS NVARCHAR(MAX)
     
  
  -- construct a cols statement for PIVOT
  select @cols = STUFF((SELECT distinct ',' + QUOTENAME([FeatureName]) 
                      from #Featuresvalue
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,'')
  
  -- construct select content for the final sql statement
  SELECT @select = STUFF((
                      SELECT distinct ' '' ' +[FeatureName] + '(''+COALESCE(b.' +QUOTENAME([FeatureName]) +',''null'') +''-''+ COALESCE(c.' + QUOTENAME([FeatureName]) + ',''null'') +'')''+'
                      from #Featuresvalue
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,'')
  
  -- remove the last +
  SELECT @select = LEFT(@select, LEN(@select)-1)
  
  
  -- final query
  SELECT @query = 'SELECT * INTO #temptable FROM
  (SELECT * FROM 
  (
  
      SELECT PartId, FeatureName, FeatureValue
      FROM #Featuresvalue
      ) x
  PIVOT
  (
      MIN(FeatureValue)
      FOR FeatureName IN ('+@cols+')
  ) piv
  ) a
  
  SELECT ' + @select + ' AS diff FROM #temp a'
  + ' JOIN #temptable b ON a.PartIdc = b.PartId'
  + ' JOIN #temptable c ON a.PartIdx = c.PartId'
  
  print @query
   EXEC(@query)
  

  Result:

  

   

  Hope helps.

  Best regards,

  Sean

  ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >