title: How do I find all combinations for an unknown number of options for a product?
body: <p>I have a table with 3 columns, Model_Name, Style_Name, and Option_Name.  How do I concatenate all available combinations of the Option_Names grouped by Model_Name and Style_Name?  What I'm trying to do is list a product with every possible combination of options in separate rows.</p>
  
  <p>I have been trying to use <code>STRING_AGG(Option_Name, ' / ')</code> WITHIN GROUP, but this just combines all Option_Names.</p>
  
  <p>The table will look like this.</p>
  
  <pre><code>Model_Name  | Style_Name | Option_Name 
  7000 Series | Front Door | White
  7000 Series | Front Door | Extra Lock 
  7000 Series | Front Door | Foam Filler
  </code></pre>
  
  <p>Ideally, we would return all of the combinations using SQL Server.</p>
  
  <pre><code>7000 Series | Front Door | White
  7000 Series | Front Door | White / Extra Lock
  7000 Series | Front Door | White / Foam Filler
  7000 Series | Front Door | White / Extra Lock / Foam Filler
  7000 Series | Front Door | Extra Lock
  7000 Series | Front Door | Extra Lock / Foam Filler
  7000 Series | Front Door | Foam Filler
  </code></pre>
  
  <p>etc.</p>
  
  <pre><code>SELECT Model_Name, Style_Name
    , STRING_AGG(CAST(Option_Name AS VARCHAR(MAX)), ' / ') WITHIN GROUP (ORDER BY Model_Name, Style_Name) Option_Name 
  FROM [dbo].[Product_Option_Master]
  GROUP BY Model_Name, Style_Name
  ORDER BY Model_Name, Style_Name
  </code></pre>
  
  <p>This just returns </p>
  
  <pre><code>7000 Series | Front Door | White / Extra Lock / Foam Filler
  </code></pre>