Hi,
Let me know which columns (names) you need and I can send you the script and if you want will try to explain as well :)
Thanks Nick
I did a bit of googling and manged to do it like this. not sure if its the most efficient method but it works (Linnworks weren't very useful) I wanted to use this in the export data but it doesnt support dynamic queries!
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(ProperyName)
FROM [StockItem_ExtendedProperties] siep
LEFT OUTER JOIN [StockItem] si ON si.pkStockItemId = siep.fkStockItemId
GROUP BY ProperyName
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query = 'SELECT ItemNumber, ItemTitle, MinimumLevel, Quantity, PurchasePrice, RetailPrice, CategoryName, Brand, Colour, Size from
(
SELECT
si.ItemNumber
, si.ItemTitle
, sl.MinimumLevel
, sl.Quantity
, sl.InOrderBook
, sl.OnOrder
, si.PurchasePrice
, si.RetailPrice
, pc.CategoryName
, siep.ProperyValue
, siep.ProperyName
from [StockItem] si
LEFT OUTER JOIN [StockItem_ExtendedProperties] siep on siep.fkStockItemId = si.pkStockItemId
LEFT OUTER JOIN ProductCategories pc on si.CategoryId = pc.CategoryId
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
LEFT OUTER JOIN ItemSupplier pp on si.pkStockItemId = pp.fkStockItemId
LEFT OUTER JOIN Supplier su on pp.fkSupplierId = su.pkSupplierID
WHERE IsArchived =''''
AND si.bLogicalDelete = ''''
AND sl.MinimumLevel > 0
AND si.RetailPrice <= 29.99
AND pc.CategoryName =''Hoody''
) x
pivot
(
MIN(ProperyValue)
for ProperyName in (' + @cols + ')
) p '
execute(@query);
cheers for the offer.
In that case if you want to add to the custom export try the below:
------------------------------------------------------------------------------------------
SELECT
si.ItemNumber AS 'SKU'
, si.ItemTitle
, sLoc.Location
, sl.MinimumLevel
, sl.Quantity
, sl.InOrderBook
,'Available' = (sl.Quantity-sl.InOrderBook)
, sl.OnOrder
, si.PurchasePrice
, si.RetailPrice
, su.SupplierName AS 'Brand'
, siep.ProperyValue AS 'Size'
, pc.CategoryName
, q1.[Ext. Prop. Colour]
,q2.[Ext. Prop. Size]
FROM
StockItem si
INNER JOIN StockItem_ExtendedProperties siep on si.pkStockItemID = siep.fkStockitemId
LEFT OUTER JOIN ProductCategories pc on si.CategoryId = pc.CategoryId
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
LEFT OUTER JOIN ItemSupplier pp on si.pkStockItemId = pp.fkStockItemId
LEFT OUTER JOIN Supplier su on pp.fkSupplierId = su.pkSupplierID
LEFT OUTER JOIN (SELECT si.ItemNumber, siep.ProperyValue AS [Ext. Prop. Colour]
FROM [StockItem] si
LEFT OUTER JOIN [StockItem_ExtendedProperties] siep ON si.pkStockItemID = siep.fkStockItemID
AND siep.ProperyName = 'Colour') q1 ON q1.ItemNumber = si.ItemNumber
LEFT OUTER JOIN (SELECT si.ItemNumber, siep.ProperyValue AS [Ext. Prop. Size]
FROM [StockItem] si
LEFT OUTER JOIN [StockItem_ExtendedProperties] siep ON si.pkStockItemID = siep.fkStockItemID
AND siep.ProperyName = 'Size') q2 ON q2.ItemNumber = si.ItemNumber
WHERE
si.bLogicalDelete = 0
AND
sl.Quantity = 0
AND
sl.MinimumLevel > 0
AND
sLoc.Location='Default'
ORDER BY
si.ItemNumber
--------------------------------------------------------------
Let me know if this is what you are after :)
Top stuff :)
All the best,
Charlie McBroom
Fitted Commerce - Ecommerce Agency and Linnworks Specialists
The Linnworks User Hub - Facebook Group for Linnworks Users
hopefully would be helpful to someone at least :)
Works perfectly. Thanks a bunch!
Ben Pearson
Hi guys looking for a little bit of help.
I need to grab a couple of extended properties for a report (Colour, Size)
I can get one property as shown in the query below but need to get two and have them in columns not rows. the query i have is as follows which pulls in one extended property. Any help would be greatly appreciated. I'm still learning :-)
SELECT
si.ItemNumber AS 'SKU'
, si.ItemTitle
, sLoc.Location
, sl.MinimumLevel
, sl.Quantity
, sl.InOrderBook
,'Available' = (sl.Quantity-sl.InOrderBook)
, sl.OnOrder
, si.PurchasePrice
, si.RetailPrice
, su.SupplierName AS 'Brand'
, siep.ProperyValue AS 'Size'
, pc.CategoryName
FROM
StockItem si
INNER JOIN StockItem_ExtendedProperties siep on si.pkStockItemID = siep.fkStockitemId
LEFT OUTER JOIN ProductCategories pc on si.CategoryId = pc.CategoryId
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
LEFT OUTER JOIN ItemSupplier pp on si.pkStockItemId = pp.fkStockItemId
LEFT OUTER JOIN Supplier su on pp.fkSupplierId = su.pkSupplierID
WHERE
si.bLogicalDelete = 0
AND
ProperyName = 'Size'
AND
sl.Quantity = 0
AND
sl.MinimumLevel > 0
AND
sLoc.Location='Default'
ORDER BY
si.ItemNumber