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

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!