[FIXED] How do I find all combinations for an unknown number of options for a product?

Issue

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.

I have been trying to use STRING_AGG(Option_Name, ' / ') WITHIN GROUP, but this just combines all Option_Names.

The table will look like this.

Model_Name  | Style_Name | Option_Name 
7000 Series | Front Door | White
7000 Series | Front Door | Extra Lock 
7000 Series | Front Door | Foam Filler

Ideally, we would return all of the combinations using SQL Server.

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

etc.

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

This just returns

7000 Series | Front Door | White / Extra Lock / Foam Filler

Solution

You need a recursive CTE:

with cte as (
      select model_name, style_name, option_name, convert(varchar(max), option_name) as option_names
      from Product_Option_Master pom
      union all
      select cte.model_name, cte.style_name, pom.option_name,
             convert(varchar(max), concat(cte.option_names, ' / ', pom.option_name))
      from cte join
           Product_Option_Master pom
           on pom.model_name = cte.model_name and
              pom.style_name = cte.style_name and
              pom.option_name > cte.option_name
     )
select *
from cte;

Here is a db<>fiddle.

Answered By - Gordon Linoff

Answer Checked By - Pedro (PHPFixing Volunteer)