tsql - How to generate all possible data combinations in SQL?

Question

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

Answer 1

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.