locked
How To show different feature values on one row separated stick if it multiple difference ? RRS feed

  • Question

  • User696604810 posted

    I work on SQL server 2012 I face issue : I can't get all different value Feature one one row result

    separated by sticky if it multiple difference

    if one different then no need stick.

    where c have value and x have value but both not equal each other

    so How can i do that Please ?

         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,
     FeatueName nvarchar(20),
     FeaatureValue int
     )
     insert into #FeatureNameandValues(PartId,FeatueName,FeaatureValue)
     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)



    Expected Result

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

    29224-more-explain-report.png

    and file excel attached ;

    http://www.mediafire.com/file/mxyr8wr9k98za7o/ExplainReport.xlsx/file

    Wednesday, September 30, 2020 6:54 AM

All replies

  • User-1330468790 posted

    Hi ahmedbarbary,

     

    From my understanding, the preliminaries are :

    1. Each row in #replace would be a group
    2. Each group has at least three feature: tall, weight, size, etc
    3. You want to display all information which is grouped by FeatureName, PartC and PartX
    4. If FeatureValuleC and FeatureValueX are different, then we should concatenate them using "-"

     

    You could refer to below sql query:

    DROP TABLE IF EXISTS #replace
    DROP TABLE IF EXISTS #FeatureNameandValues
    DROP TABLE IF EXISTS #Temp
    DROP TABLE IF EXISTS #Temp1
    
    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], 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
    
     SELECT a.[FeatureName] [FeatureName], 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]
    
    
    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]
            
    FROM #Temp1 T1  
    GROUP BY T1.[FeatureName], T1.PartC,T1.PartX
    ORDER BY PartC, PartX
    

    Result:

     

    Related Link about concatenating the string for group by:

    https://stackoverflow.com/a/18910357/12871232

     

    Hope this can help you.

    Best regards,

    Sean

    Thursday, October 1, 2020 7:29 AM
  • User696604810 posted

    thank you for reply this is good

    but result i need to display rows as one result as below ;

    so How to do please 

    Tall (80-20) | Weight(5-10) | Tall(190-130)
    Saturday, October 3, 2020 11:47 AM
  • User-1330468790 posted

    Hi ahmedbarbary,

     

    Then you might need to do one more action. 

    I reconstruct the sql statement to ensure that the value for part C will always be before the value for part X.

     

    More details, you could refer to below codes:

    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]

    Result:

     

    Hope helps.

    Best regards,

    Sean

    Tuesday, October 6, 2020 8:08 AM