How to create a SQL query for the following operation?

Question

I hope you will be able to help me out. I just started learning SQL and while applying my knowledge at work, I got stuck.

I have SQL Database with multiple tables, which contain various data for properties of the items offered for sale. So far I successfully created a query which pulls most of the information needed. Unfortunately, the last table became problematic for me.

Table is formatted like this:

| fkStockItemId  | PropertyName | PropertyValue | PropertyType |
|   ItemSKU-1    |  Item Style  |   SB-01123    |   Attribute  |
|   ItemSKU-1    |  Item Size   |    X-Small    |   Attribute  |
|   ItemSKU-1    |  Item Color  |      Red      |   Attribute  |
|   ItemSKU-2    |  Item Style  |   AA-66002    |   Attribute  |
|   ItemSKU-2    |  Item Size   |    Medium     |   Attribute  |
|   ItemSKU-2    |  Item Color  |     Green     |   Attribute  |                            
|   ItemSKU-3    |  Item Style  |    110445     |   Attribute  |
|   ItemSKU-3    |  Item Size   |     Small     |   Attribute  |

Output I am trying to get is like this:

    |    SKU    |  Item Style  |  Item Size  | Item Color  | 
    | ItemSKU-1 |   SB-01123   |   X-Small   |    Red      |
    | ItemSKU-2 |   AA-66002   |   Medium    |    Green    |
    | ItemSKU-3 |    110445    |    Small    |   *Null*    | 


    Please note that last column "PropertyType" is for technical purposes and 
is not needed to be queried.

This is what I got so far:

SELECT si.ItemNumber, si.ItemTitle, si.ItemDescription, si.RetailPrice, si.Weight, sl.Quantity, c.CategoryName, siep.ProperyValue, siep.ProperyName
FROM StockItem si
LEFT OUTER JOIN StockLevel sl ON si.pkStockItemID = sl.fkStockItemId
LEFT OUTER JOIN ProductCategories c ON si.CategoryId = c.CategoryId
LEFT OUTER JOIN StockItem_ExtendedProperties siep ON si.pkStockItemID = siep.fkStockItemId
WHERE siep.ProperyName = 'Item Style'

Tables "StockLevel" and "ProductCategories" show results just fine. If you notice, last "StockItem_ExtendedProperties" JOIN and "siep.ProperyValue", "siep.ProperyName" coupled with "WHERE siep.ProperyName = 'Item Style'" only allowed me to query 1 property. Thank you for your help and time!

Answer 1

Move the propertyname filter to the ON clause from the WHERE clause. Then join again for each property:

SELECT si.ItemNumber, si.ItemTitle, si.ItemDescription, si.RetailPrice, si.Weight, sl.Quantity, c.CategoryName, style.ProperyValue as style, size.ProperyValue as size
FROM StockItem si
LEFT OUTER JOIN StockLevel sl ON si.pkStockItemID = sl.fkStockItemId
LEFT OUTER JOIN ProductCategories c ON si.CategoryId = c.CategoryId
LEFT OUTER JOIN StockItem_ExtendedProperties style ON si.pkStockItemID = style.fkStockItemId
AND style.ProperyName = 'Item Style'
LEFT OUTER JOIN StockItem_ExtendedProperties size ON si.pkStockItemID = size.fkStockItemId
AND size.ProperyName = 'Item Size'

Answer 2

To get each value in a column like that, you can first write a subquery for each property, and then JOIN them all together, like this:

SELECT m1.fkStockItemId, m1.propertyValue AS 'Item Style', m2.propertyValue AS 'Item Size', m3.propertyValue AS 'Item Color'
FROM(
  SELECT fkStockItemId, propertyValue
  FROM myTable
  WHERE propertyName = 'Item Style') m1
LEFT JOIN(
  SELECT fkStockItemId, propertyValue
  FROM myTable
  WHERE propertyName = 'Item Size') m2 ON m2.fkStockItemId = m1.fkStockItemId
LEFT JOIN(
  SELECT fkStockItemId, propertyValue
  FROM myTable
  WHERE propertyName = 'Item Color') m3 ON m3.fkStockItemId = m2.fkStockItemId;

Here is an SQL Fiddle example.

Answer 3

I believe that the best way is to make a function that will return the value of property you need and what you include in the query.

CREATE FUNCTION dbo.GetItemProperty
(
    @ItemName AS VARCHAR(50)
    , @Property AS VARCHAR(8)
)
RETURNS VARCHAR(50)
AS
BEGIN

    DECLARE @Ans AS VARCHAR(50) = ''

    SELECT  @Ans = PropertyValue 
    FROM    StockItem AS si
    JOIN    StockItem_ExtendedProperties AS siep 
            ON si.pkStockItemID = siep.fkStockItemId
    WHERE   si.pkStockItemID = @ItemName
            AND siep.ProperyName = @Property

    RETURN @Ans;

END
GO

SELECT  si.ItemNumber, 
        si.ItemTitle, 
        si.ItemDescription, 
        si.RetailPrice, 
        si.Weight, 
        sl.Quantity, 
        c.CategoryName, 
        dbo.GetItemProperty(si.pkStockItemID, 'Item Style') AS 'Item Style', 
        dbo.GetItemProperty(si.pkStockItemID, 'Item Size') AS 'Item Size', 
        dbo.GetItemProperty(si.pkStockItemID, 'Item Color') AS 'Item Color'
FROM StockItem si
LEFT OUTER JOIN StockLevel sl 
    ON si.pkStockItemID = sl.fkStockItemId
LEFT OUTER JOIN ProductCategories c 
    ON si.CategoryId = c.CategoryId
LEFT OUTER JOIN StockItem_ExtendedProperties siep 
    ON si.pkStockItemID = siep.fkStockItemId;

God luck!