Merging duplicate table rows into 1 using a common primary key

Hi guys,


A quick work around on the below sql statement will be highly appreciated.


I am getting duplicate rows on a single Id because the column data are displayed on different rows. could someone kindly look into it and help me know how i can merge the duplicate rows into single row i.e


in the attached image the first 4 rows with the skuId- (d2d Trilby Hats 4664 (62)) should merge into 1 row and the next 4 rows with the skuId -(

 Remington H5670) should merge into one single row. Thanks in advace


**My SQL statemet**

    

    WITH add_id as (

     SELECT top 10

     vr.ItemNumber as ProductId,

     si.ItemNumber as skuId,

     si.BarcodeNumber AS EAN,

     c.CategoryName AS Category,

     si.pkStockItemID as joiningId,

     case 

     when

     vr.ItemTitle IS null then t.Title

     else 

     vr.ItemTitle

     end

     as Title,

     si.ItemDescription,

     t.Source AS Source,

     t.SubSource AS SubSource,

     p.SalePrice,

     sir.IsMain as main,

     case

     when sir.pkImageId IS null then 'No Image URL'

     else 

     lower('http://xxxxxxxxx/'+CONVERT(NVARCHAR(32),HASHBYTES('MD5', CONVERT(varchar(max), DB_NAME())), 2)+'/'+CONVERT(NVARCHAR(max), sir.pkImageId)+'.jpg') 

     end 

     AS Image_URL

     FROM           

     [StockItem] si

     inner join Stock_ImageReg sir ON si.pkStockItemID =sir.fkStockItemId

     LEFT OUTER JOIN StockItem_Variations sv ON si.pkStockItemID = sv.fkStockItemID

     LEFT OUTER JOIN VariationItem_View vr ON sv.fkVariationItemId = vr.pkStockItemid

     INNER JOIN StockItem_Titles t on si.pkStockItemID= t.fkStockItemId

     INNER JOIN ProductCategories c ON si.CategoryId= c.CategoryId

     LEFT OUTER JOIN StockItem_Pricing p ON si.pkStockItemID = p.fkStockItemId AND t.Source=p.Source AND t.SubSource=p.SubSource

     

     WHERE

     si.bLogicalDelete = 0 AND

     t.Source= 'FRUUGO' 

    ),

    

    extprop AS

    (

     SELECT distinct

     case 

     when

     [ProductId] IS null then [skuId]

     else 

     [ProductId]

     end

     as [ProductId],

     [skuId],

     EAN,

     MAX([Image URL1]) as [Image URL1],

     MAX([Image URL2]) as [Image URL2],MAX([Image URL3]) as [Image URL3],MAX([Image URL4]) as [Image URL4],MAX([Size]) as Size,

     MAX([Design]) as Design,MAX([Weight])as [Weight],MAX([Colour]) as [Colour], MAX( [Image]) as [Image],MAX( [Colour (Swap)]) as [Colour (Swap)],

     MAX( [Brand]) as [Brand], MAX ([Age Level]) as [Age Level], MAX ([Boot Size]) as [Boot Size],MAX ([Letter (Swap)]) as [Letter (Swap)], 

     MAX ([Letter/Number (Swap)]) as [Letter/Number (Swap)] , MAX ( [Number (Swap)]) as [Number (Swap)],MAX([Number in Pack]) as [Number in Pack],

     MAX([Package Quantity]) as [Package Quantity], MAX([Word (Swap)]) as [Word (Swap)],MAX([List on Fruugo]) as [List on Fruugo]

     FROM

     (

      SELECT [ProductId],[skuId],EAN,[Title],ItemDescription,Category,SalePrice,Source,SubSource,

      MAX(CASE WHEN ProductId IS NUll THEN [Image_URL]

       ELSE 

       CASE WHEN rn = 1 THEN [Image_URL]

       else 'No Image'

       END

       END

      )as [Image URL1],

      MAX(CASE WHEN rn = 1 THEN [Image_URL] END) as [Image URL22],

      MAX(CASE WHEN rn = 2 THEN [Image_URL] END) as [Image URL2],

      MAX(CASE WHEN rn = 3 THEN [Image_URL] END) as [Image URL3],

      MAX(CASE WHEN rn = 4 THEN [Image_URL] END) as [Image URL4],

      MAX(CASE WHEN rn = 5 THEN [Image_URL] END) as [Image URL5],

      MAX(CASE WHEN rn = 6 THEN [Image_URL] END) as [Image URL6],

      MAX(CASE WHEN rn = 7 THEN [Image_URL] END) as [Image URL7],

      MAX(CASE WHEN rn = 8 THEN [Image_URL] END) as [Image URL8],

      MAX(CASE WHEN rn = 9 THEN [Image_URL] END) as [Image URL9],

      MAX(CASE WHEN rn = 10 THEN [Image_URL] END) as [Image URL10],

      MAX(CASE WHEN rn = 11 THEN [Image_URL] END) as [Image URL11],

      MAX(CASE WHEN rn = 12 THEN [Image_URL] END) as [Image URL12],MAX([Size]) as [Size],MAX([Design]) as [Design],MAX([Weight])as [Weight],

      MAX([Colour]) as [Colour], MAX( [Image]) as [Image],MAX( [Colour (Swap)]) as [Colour (Swap)], MAX( [Brand]) as [Brand],

      MAX ([Age Level]) as [Age Level], MAX ([Boot Size]) as [Boot Size],MAX ([Letter (Swap)]) as [Letter (Swap)],MAX ([Letter/Number (Swap)]) as [Letter/Number (Swap)],

      MAX ([Number (Swap)]) as [Number (Swap)],MAX([Number in Pack]) as [Number in Pack], MAX([Package Quantity]) as [Package Quantity],

      MAX([Word (Swap)]) as [Word (Swap)],MAX( [List on Fruugo]) as [List on Fruugo]

      FROM

      (

       SELECT MAX ([ProductId])as ProductId,[skuId],EAN,[Title],ItemDescription,Category,SalePrice,Source,SubSource,[Image_URL],main,joiningId, MAX (siep.ProperyName) AS PropertyName ,

       MAX (siep.ProperyValue) AS PropertyValue,row_number() over (partition by [skuId] ORDER BY [skuId]) as rn

       FROM add_id id

       LEFT OUTER JOIN StockItem_ExtendedProperties siep on joiningId= siep.fkStockItemId

       WHERE

       siep.ProperyName = 'Size' OR siep.ProperyName = 'Weight' OR siep.ProperyName = 'Colour' OR siep.ProperyName = 'Brand' OR siep.ProperyName = 'Age Level'

       OR siep.ProperyName = 'Boot Size' OR siep.ProperyName = 'Letter (Swap)' OR siep.ProperyName = 'Letter/Number (Swap)'OR siep.ProperyName = 'Number (Swap)' 

       OR siep.ProperyName = 'Number in Pack'OR siep.ProperyName = 'Package Quantity'OR siep.ProperyName = 'Word (Swap)' OR siep.ProperyName ='List on Fruugo'

       

       GROUP BY ProductId,[skuId],EAN,[Title],ItemDescription,Category,SalePrice,Source,SubSource,[Image_URL],main,joiningId,siep.ProperyName,siep.ProperyValue

       

      )AS Dertbl

      PIVOT (MAX (PropertyValue) FOR [PropertyName] IN ([Size],[Design],[Weight],[Colour],[Image],[Colour (Swap)], [Brand],[Age Level], [Boot Size], [Letter (Swap)], [Letter/Number (Swap)], [Number (Swap)], [Number in Pack], [Package Quantity], [Word (Swap)],[List on Fruugo])

     )AS PVT 

     Group by [ProductId],[skuId],EAN,[Title],Source,SubSource,Category,SalePrice,main,ItemDescription,[Size],[Design],[Weight],

     [Colour],[Image],[Colour (Swap)], [Brand],[Age Level], [Boot Size], [Letter (Swap)], [Letter/Number (Swap)], [Number (Swap)], 

     [Number in Pack], [Package Quantity], [Word (Swap)],[List on Fruugo]

    ) tb

    

    GROUP BY ProductId,[ProductId],[skuId],EAN,[Title],ItemDescription,Category,SalePrice,Source,SubSource,[Size],[Design],[Weight],

    [Colour],[Image],[Colour (Swap)], [Brand],[Age Level], [Boot Size], [Letter (Swap)], [Letter/Number (Swap)], [Number (Swap)], 

    [Number in Pack], [Package Quantity], [Word (Swap)],[List on Fruugo]

   )

   

   SELECT *

   FROM 

   extprop 

  


**[CURRENT RESULTS][1]**



  [1]: https://i.stack.imgur.com/lCohZ.png


img.png img.png
49.4 KB

Login to post a comment