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.

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