如何为产品的未知数量选项找到所有组合?

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

来源: link 标签:sqlsql-servercombinationsproductoptions 更新时间:

  问题

我有一个包含3列,Model_Name,Style_Name和Option_Name的表.如何连接按Model_Name和Style_Name分组的Option_Name的所有可用组合?我想要做的是列出一个产品,每个可能的选项组合在单独的行中.

我一直在尝试使用STRING_AGG(Option_Name, ' / ') WITHIN GROUP,但这只是结合了所有Option_Names.

这张桌子看起来像这样。 

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

理想情况下,我们将使用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

等等 

 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

这只是返回 

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

  最佳答案

您需要递归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 是一个db 小提琴.

  相同标签的其他问题

sqlsql-servercombinationsproductoptions
上一个问题:
下一个问题: