Merging duplicate table rows into 1 using a common primary key
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 ethan@hypercommerce.co.uk
Ossie Smith
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