Sql-server – How to pivot a table then join multiple tables and change values based on information between tables

join;pivotsql server

The database I'm using has multiple tables storing different information types…it's very convoluted.

I'm trying to join this information into one table, with all of the details for a single ProductID on a single line. I know I need some combination of a pivot and joins, but I'm struggling with changing the values in the pivoted columns.

My Raw Tables are:

Inventory Table:

ProductID     ProductName
---------     -------------------------
S1810944      VIOLA ENDURIO PURE YELLOW
S1810949      ACHILLEA SUMMER PASTELS
S1841708      SPEARMINT

Property Table:

ID          PropertyName
----------- ------------
592         Category
595         Product Type
598         Sale
600         Group

PropertyChoice Table (each PropertyID from above has multiple choices):

ID          PropertyID  ChoiceName
----------- ----------- ------------
541         595         ORGANIC
544         595         REFINED
545         595         STANDARD
1050        598         Yes
1051        598         No
1509        592         FLOWER
1512        592         HERB
1502        600         ANNUAL
1503        600         PERENNIAL

and finally an InventoryProperty table (associating Inventory items to Property choices for a particular property):

ProductID       PropertyID  PropertyValue
-------------   ----------- -------------
S1810944        592         1509
S1810944        598         1051
S1810944        600         1502
S1810944        595         545
S1810949        592         1509
S1810949        598         1051
S1810949        600         1503
S1810949        595         544
S1841708        592         1512
S1841708        598         1050
S1841708        600         1502
S1841708        595         541

The output I'm going for should look like this:

ProductID     ProductName     Category  ProductType     Sale    Group
---------     ------------    --------- ------------    ------  -----------
S1810944      VIOLA           FLOWER    STANDARD        No      ANNUAL
S1810949      ACHILLEA        FLOWER    REFINED         No      PERENNIAL
S1841708      SPEARMINT       HERB      ORGANIC         Yes     ANNUAL

But the closest I have achieve are the Property Values listed like this:

ID          ProductName Category    ProductType Sale    Group
--------    ----------- --------    ----------- ----    -----
S1810944    VIOLA       1509        545         1051    1502
S1810949    ACHILLEA    1509        544         1051    1503
S1841708    SPEARMINT   1512        541         1050    1502

using this query:

SELECT 
    I.ProductID,
    I.ProductName,
    P.[592] as Category,
    P.[595] as ProductType,
    P.[598] as Sale,
    P.[600] as [Group]
FROM InventoryProperty
PIVOT
    (
    MAX(PropertyValue)
    FOR PropertyID IN ([592],[595],[598],[600])
    ) as P
INNER JOIN Inventory as I on P.ProductID = I.ProductID

Obviously, I need to tie in the ChoiceName values on from the PropertyChoice table, but I'm not sure how to go about doing that without using CASE…I really want to avoid that because in my real tables, I have over 450 ChoiceName values. I'd also like to make this a query I can reuse even if I add in some new Choice names in the future.

Best Answer

Nevermind, I solved it myself using a short series of joins from the pivot fields:

SELECT 
    I.ProductID,
    I.ProductName,
    A.ID as Category,
    B.ID as ProductType,
    C.ID as Sale,
    D.ID as [Group]
FROM InventoryProperty
PIVOT
    (
    MAX(PropertyValue)
    FOR PropertyID IN ([592],[595],[598],[600])
    ) as P
INNER JOIN Inventory as I on P.ProductID = I.ProductID
INNER JOIN PropertyChoice as A on P.[592] = A.ID
INNER JOIN PropertyChoice as B on P.[595] = B.ID
INNER JOIN PropertyChoice as D on P.[598] = C.ID
INNER JOIN PropertyChoice as E on P.[600] = D.ID
Related Question