title: How to generate all possible data combinations in SQL?
body: <p>I'm developing a shop web site. I have a database table, <strong>ProductOption</strong>, that represents the various options that are applicable to a product (Sizes, Colours etc):</p>
  
  <pre><code>ProductOptionId
  ProductOptionGroupId
  ProductId
  Value
  </code></pre>
  
  <p>(I've simplified this for brevity)</p>
  
  <p>ProductOptionGroupId links to the ProductOptionGroup table; where a group is <em>Size</em>, <em>Colour</em> etc, and the Value is, well, the value - <em>Black, Red, Large, Small</em> etc.</p>
  
  <p>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):</p>
  
  <pre><code>ProductOptionId, ProductOptionGroupId, Value
  1, 1, Red
  2, 1, Black
  3, 1, Green
  4, 2, Large
  5, 2, Small
  6, 3, Long
  7, 3, Short
  </code></pre>
  
  <p>I'd need to generate data representing the following:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>...basically every possible combination, within the groups.</p>
  
  <p>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).</p>
  
  <p>How can I achieve this, using SQL Server 2005?</p>
  
  <p>Thanks</p>