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?
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.
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:
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. :)
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
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.