Product Attributes to Columns?

[CoginAustin]

I have 4 tables


Product_Group
------------------
ProdGroup_ID
Group_Name

Products
-----------
Product_ID
ProdGroup_ID
Product_Name

Prod_Attributes
____________
Attrib_ID
Product_ID
Attribute_Name

Attribute_Values
--------------------
Attrib_ID
Attrib_Value


I want to take a Product Group(Group A) and extract all the Products, Attributes, and Values.

I need to take the Attribute_Name and basically turn them into columns.

So I would have a dimension Products with a Fact table consisting of most of the attributes of that product(Name, Qty, Price) with the Attribute_Values filling the rows of the facts.

Each row of Attribute_Values is only 1 attribute with 1 value. There may be 20 attributes for this product group. How can I take those 20 attribute_values and extract them as 1 row so I place them into the Fact columns?

Also, each product has only 1 set of attributes and values per group.

Something like the following works but I would imagine it is not the best:

SELECT   
g.Product_group,
p.Product_ID,
(SELECT attrib_value FROM tbl_values v WHERE v.Product_ID=p.Product_ID AND attrib_id=1) as Quantity,
(SELECT attrib_value FROM tbl_values v WHERE v.Product_ID=p.Product_ID AND attrib_id=2) as Price,
(SELECT attrib_value FROM tbl_values v WHERE v.Product_ID=p.Product_ID AND attrib_id=3) as Cost,
FROM   Product_Group g
INNER JOIN products p ON p.ProdGroup_ID=g.ProdGroup_ID
WHERE p.ProdGroup_ID=2



If you can imagine the attributes ranging from 20-250.. Well, it would get very long, messy, and kill the server.

Any suggestions?

[GoWestGw]

The easiest approach is to Pivot the data.  You might have to set-up up to 250 pivot buckets, but this approach works and is quick.

[CoginAustin]

Is it really quick? What got me was the number of buckets I may have to create. I didnt want to go through the hassle of creating it just to see it crawl.

Basically the way I ended up doing it was below. Loading the fact table it complete 2.5 million rows in 1.20 minutes which is pretty satisfactory in my mind:

Code Select Expand


SELECT
Ã,  d1.barcode_id,
Ã,  d1.Attrib1,
Ã,  d1.Attrib2,
Ã,  d1.Attrib3,
etc....

FROM
Ã,  (SELECT
Ã,  Ã,  p.barcode_id,
Ã,  Ã,  MAX((
Ã,  Ã,  CASE
Ã,  Ã,  Ã,  WHEN(v.attrib_id=499)
Ã,  Ã,  Ã,  THEN attrib_value
Ã,  Ã,  END
Ã,  Ã,  )) AS Attrib1,
Ã,  Ã,  MAX((
Ã,  Ã,  CASE
Ã,  Ã,  Ã,  WHEN(v.attrib_id=539)
Ã,  Ã,  Ã,  THEN attrib_value
Ã,  Ã,  END
Ã,  Ã,  )) AS Attrib2,
Ã,  Ã,  MAX((
Ã,  Ã,  CASE
Ã,  Ã,  Ã,  WHEN(v.attrib_id=736)
Ã,  Ã,  Ã,  THEN attrib_value
Ã,  Ã,  END
Ã,  Ã,  )) AS Attrib3

etc ....

Ã,  FROMÃ,  Ã, Product p
Ã,  INNER JOIN Values v ON v.productid=p.id
Ã,  GROUP BY p.id
Ã,  )
Ã,  [D1]


Thanks for a response though! I was beginning to think I was the only one here using DS. :)

[Blue]

Not a bad solution.

I would have done it using union statements.  E.g.

SELECT barcode_id, SUM(measure1) AS measure1, SUM(measure2) AS measure2, ...
FROM
  (SELECT T1.barcode_id, ISNULL(T2.attrib,0) AS measure1, CAST(0 AS FLOAT) AS measure2, ...
     FROM product T1, values T2
    WHERE T1.id = T2.productid AND T2.attrib_id = 499
   UNION
   SELECT T1.barcode_id, CAST(0 AS FLOAT) AS measure1, ISNULL(T2.attrib,0) AS measure2, ...
     FROM product T1, values T2
    WHERE T1.id = T2.productid AND T2.attrib_id = 539
   UNION
   ...
  ) T0
GROUP BY barcode_id

[Blue]

Oops.  I should made the table joins as LEFT OUTER JOINS and not implicit INNER JOINS.  This would make shure I got ALL measuren columns.