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.
Hello Ossie,
This is Ethan from HyperCommerce. Are you still hoping to get all extended properties along with StockItem details?
You could do this using a DYNAMIC Query and SQL Pivot such as this:
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) SELECT @cols = STUFF((SELECT ',' + QUOTENAME(e.ProperyName) FROM [StockItem_ExtendedProperties] e LEFT OUTER JOIN [StockItem] s ON s.pkStockItemId = e.fkStockItemId GROUP BY e.ProperyName FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') SET @query = 'SELECT ItemNumber, ItemTitle, ' + @cols + ' from ( SELECT s.ItemNumber, s.ItemTitle, e.ProperyValue, e.ProperyName from [StockItem] s LEFT OUTER JOIN [StockItem_ExtendedProperties] e on e.fkStockItemId = s.pkStockItemId WHERE IsArchived ='''' AND bLogicalDelete = '''' ) x pivot ( MIN(ProperyValue) for ProperyName in (' + @cols + ') ) p ' execute(@query);
If you need custom solutions such as Advanced SQL Queries, Rules Engine, Custom Reports, API Solutions or Integrations, please feel free to reach me at ethan@hypercommerce.co.uk
Ossie Smith
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