2

I'm developing a shop web site. I have a database table, ProductOption, that represents the various options that are applicable to a product (Sizes, Colours etc):

ProductOptionId
ProductOptionGroupId
ProductId
Value

(I've simplified this for brevity)

ProductOptionGroupId links to the ProductOptionGroup table; where a group is Size, Colour etc, and the Value is, well, the value - Black, Red, Large, Small etc.

I need to generate all possible combinations of product options, restricted by group, for each product in the database. Imagine the following data for product 1 (where group 1=Colour, 2=Size and 3=Length):

ProductOptionId, ProductOptionGroupId, Value
1, 1, Red
2, 1, Black
3, 1, Green
4, 2, Large
5, 2, Small
6, 3, Long
7, 3, Short

I'd need to generate data representing the following:

Red, Large, Long
Black, Large, Long
Green, Large, Long
Red, Small, Long
Black, Small, Long
Green, Small, Long
Red, Large, Short
Black, Large, Short
Green, Large, Short
Red, Small, Short
Black, Small, Short
Green, Small, Short

...basically every possible combination, within the groups.

There can be any number of groups, and any number of products options for each product. I need to be able to generate the results by only knowing the ProductId ahead of time (i.e. give me all product option combinations for this product).

How can I achieve this, using SQL Server 2005?

Thanks

Leigh Bowers
  • 707
  • 10
  • 22
  • do you need the result as three columns? – stian.net Jan 21 '11 at 10:37
  • Not specifically. I just need some way of returning all product option combinations for any given product. One row per option is fine, but I'd need to be able to group the combinations together. – Leigh Bowers Jan 21 '11 at 11:13

3 Answers3

3

Well, you are in kinda of a problem there, specially if you don't know wich groups are attached to wich products ahead of time. It appears to me that you are gonna need two of the biggest things a try to avoid in sql: cursors, and dynamic sql. Before using the following solution, you should take a look at this link The Curse and Blessings of Dynamic SQL. Then, you can try this:

DECLARE @ProductId INT, @Query NVARCHAR(MAX), @ProductOptionGroupId INT
SET @ProductId = 1
SET @Query = ''

DECLARE CC CURSOR FOR
SELECT DISTINCT ProductOptionGroupId
FROM YourTable
WHERE ProductId = @ProductId

OPEN CC
FETCH NEXT FROM CC INTO @ProductOptionGroupId
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Query = @Query + '(SELECT DISTINCT Value FROM YourTable WHERE ProductOptionGroupId='+CAST(@ProductOptionGroupId AS VARCHAR)+'AND ProductId='+CAST(@ProductId AS VARCHAR)+
                 +') AS Table' + CAST(@ProductOptionGroupId AS VARCHAR)+' CROSS JOIN '

    FETCH NEXT FROM CC INTO @ProductOptionGroupId
END
CLOSE CC
DEALLOCATE CC
SET @Query = 'SELECT * FROM ' + LEFT(@Query,LEN(@Query)-10)

PRINT @Query
EXEC sp_executesql @Query

Let me know how it goes.

Lamak
  • 69,480
  • 12
  • 108
  • 116
1
SELECT PO1.Value, PO2.Value, PO3.Value
FROM ProductOption PO1, ProductOption PO2, ProductOption PO3 
WHERE PO1.ProductOptionGroupID = 1
AND PO2.ProductOptionGroupID = 2
AND PO3.ProductOptionGroupID = 3
eumiro
  • 207,213
  • 34
  • 299
  • 261
  • I've updated the question, to better clarify what I need. I won't know which options (and therefore groups) are attached to which products ahead-of-time. I need to be able to say "give me all product option combinations for this product" (i.e. WHERE ProductId = n). In my example, there are three groups, but there will, in reality, be many more. Sorry. I found it quite difficult to describe my requirements. – Leigh Bowers Jan 21 '11 at 11:31
0

in SQLServer you can use CUBE Operator, that return a resultset with all combination... but I don't know if you meaning that...

Hope this helps

Nicola

Nicola
  • 23
  • 1
  • 5