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

  • Question

  • User696604810 posted

    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)

    Monday, March 1, 2021 10:50 PM

All replies

  • User-1330468790 posted

    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

    Tuesday, March 2, 2021 7:37 AM