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,



     vr.ItemTitle IS null then t.Title




     as Title,


     t.Source AS Source,

     t.SubSource AS SubSource,


     sir.IsMain as main,


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


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


     AS Image_URL


     [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



     si.bLogicalDelete = 0 AND

     t.Source= 'FRUUGO' 



    extprop AS


     SELECT distinct



     [ProductId] IS null then [skuId]




     as [ProductId],



     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]



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

      MAX(CASE WHEN ProductId IS NUll THEN [Image_URL]


       CASE WHEN rn = 1 THEN [Image_URL]

       else 'No Image'



      )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]



       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


       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]









img.png img.png
49.4 KB

Hello Ossie,

This is Ethan again from HyperCommerce. 

I'd suggest using an UNPIVOT after your PIVOT to resolve your issue.

If you need custom solutions such as Advanced SQL Queries, Custom Reports, API Solutions or Integrations, please feel free to reach me at

Login to post a comment