Как создать SQL-запрос для следующей операции?

Question

Я надеюсь, что вы сможете мне помочь. Я только начал изучать SQL и, применяя свои знания на работе, застрял.

У меня есть база данных SQL с несколькими таблицами, которые содержат различные данные о свойствах предметов, предлагаемых к продаже. До сих пор я успешно создал запрос, который извлекает большую часть необходимой информации. К сожалению, последний стол стал проблематичным для меня.

Таблица отформатирована так:

| 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  |

Вывод, который я пытаюсь получить, выглядит так:

    |    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.

Это то, что я получил так далеко:

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'

Таблицы "StockLevel" и "ProductCategories" показывают результаты просто отлично. Если вы заметили, последние JOIN "StockItem_ExtendedProperties" и "siep.ProperyValue", "siep.ProperyName" в сочетании с "WHERE siep.ProperyName = 'Item Style'" позволили мне запросить только 1 свойство. Спасибо за вашу помощь и время!

Answer 1

Переместите фильтр имени свойства в предложение ON из предложения WHERE. Затем присоединитесь снова для каждого свойства:

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

Я считаю, что лучший способ - создать функцию, которая будет возвращать значение свойства, которое вам нужно, и то, что вы включаете в запрос.

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;

Удачи!

Answer 3

Чтобы получить каждое значение в таком столбце, вы можете сначала написать подзапрос для каждого свойства, а затем JOIN они все вместе, вот так:

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;

Вот пример SQL Fiddle.