sql Query to loop through Extended properties

Hello People, Urgent help here would be appreciated,


I am writing a script to get the following values from Linnworks DB,

         SKU;

         ProductTitle;

         ItemID;

         ItemDescription;

         RetailPrice;

         PurchasePrice;

         VAT;

         CategoryId;

         CategoryName;

         PropertyName;

         Brand;

         Colour;

         Size;

         Location;

unfortunately some of the values are stored as extended properties (colour, brand, size) which i need to iterate through, current my script allows me to pick one extended property at a time. technically what i need is to loop through all the available extended properties of a product and extract the needed properties. My script is as below


 SELECT 

 si.ItemNumber,

 si.ItemTitle,

 si.pkStockItemId,

 si.ItemDescription,

 si.RetailPrice,

 si.PurchasePrice,

 si.TaxRate,

 si.CategoryId,

 c.CategoryName,

 siep.ProperyName,

 siep.ProperyValue AS Brand,

 sLoc.Location,

 sl.Quantity AS Stock_Levels,

    ROW_NUMBER() OVER(ORDER BY sl.Quantity ASC)

FROM

 [StockItem] si

 LEFT OUTER JOIN StockLevel sl ON si.pkStockItemId = sl.fkStockItemId

 LEFT OUTER JOIN StockLocation sLoc ON sl.fkStockLocationId = sLoc.pkStockLocationId

 LEFT OUTER JOIN ItemLocation il ON il.fkStockItemId = si.pkStockItemId AND il.fkLocationId = sLoc.pkStockLocationId

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

 INNER JOIN StockItem_ExtendedProperties siep on si.pkStockItemID = siep.fkStockItemId

WHERE

 si.bLogicalDelete = 0 AND

 siep.ProperyName= 'Brand' ------------------> (I need to extract more extended properties)

ORDER BY

 si.ItemNumber,

 sLoc.Location



WHERE 

si.bLogicalDelete = 0 AND siep.ProperyName IN ('Brand', 'Colour', 'Size')


Not sure if it will work though.

Hi Phil, 

This works fine but I need to extract the exact values and display on a table just like in custom query, have all the extended properties in columns. 

Just a little more idea and all will be fine. 


Kind Regards

Hi Ossie,


I see what you mean, not an easy one to resolve for me however. I've moved to mysql more now so would have thought something like a group_concat statement would of helped however there is no direct SQL equivalent. 


When I get to this point I would put the question to one of the jobbing websites to get the result, fiverr is my favourite. Give the existing statement with some results to show that you want columns for the extended properties and someone would normally be able to help.


Regards,

Phil.




 


Login to post a comment