SQL Server : permutations/combinations without looping

Question

I have two data sets. The first is a table of product recipes along with the products that make up the recipe. The 2nd data set contains individual pricing by product (I can have multiple prices for a single product).

What I'm trying to achieve is to output a result set that contains the unique permutations for each of my product recipes. Only recipes where ALL of the components have pricing in the 2nd data set should be in the output.

Assumption: A single recipe can have up to 5 components configured (no more).

DECLARE @ProductRecipe TABLE (ProductRecipeID INT, ComponentProductID INT)

INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) 
VALUES (21, 130), (21, 468), (21, 500), 
       (22, 468), (22, 500), 
       (23, 130), (23, 501)

DECLARE @ComponentPricing TABLE (PricingID INT, ProductID INT)

INSERT INTO @ComponentPricing (PricingID, ProductID)
VALUES (314023, 130), (313616, 130), (313071, 130),
       (312865, 130), (316323, 468), (316329, 468), (398864, 500)

I would like my output to look like this:

Output Example

I have tried CTEs and self joins but I can't even get close to my desired output.. :(

I’m using SQL Server 2012

If I understand what you want, it sounds like you can accomplish it with five for-loops--or, more generally, with while-loops. For each first component, and for each second component, etc., you want the recipe they produce. https://stackoverflow.com/questions/6069024/syntax-of-for-loop-in-sql-server#6069059 — Addem, Dec 22 '18 at 05:59

score 0 · Accepted Answer · answered Dec 22 '18 at 06:49

I'm going to assume you are working with SQL Server 2008 or newer, which is required to make the dense_rank() function work.

The solution below goes through a few steps that are outlined in the comments. One call out is that I changed one of the @ProductRecipe records from (22, 130) to (22, 468) as I believe it to be the intended sample data because Component1 of the desired output includes PricingID values 316323 and 316329.

Answer:

DECLARE @ProductRecipe TABLE (ProductRecipeID INT, ComponentProductID INT)
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) VALUES (21, 130)
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) VALUES (21, 468)
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) VALUES (21, 500)
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) VALUES (22, 468) --values were (22, 130) in question
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) VALUES (22, 500)
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) VALUES (23, 130)
INSERT INTO @ProductRecipe (ProductRecipeID, ComponentProductID) VALUES (23, 501)

DECLARE @ComponentPricing TABLE (PricingID INT, ProductID INT)
INSERT INTO @ComponentPricing (PricingID, ProductID)
VALUES (314023, 130)
 , (313616, 130)
 , (313071, 130)
 , (312865, 130)
 , (316323, 468)
 , (316329, 468)
 , (398864, 500)

; with base as
    (
        --Joining the two datasets together.
        select pr.ProductRecipeID
        , pr.ComponentProductID
        , cp.PricingID
        from @ProductRecipe as pr
        left join @ComponentPricing as cp on pr.ComponentProductID = cp.ProductID   
    )
    , pr_exclude as
    (
        --Identifying that ProductRecipeID 23 should be excluded because of the 501 NULL value
        select distinct b.ProductRecipeID
        from base as b
        where b.PricingID is null   
    )
    , final_base as
    (
        --Assigning Rank to each ComponentProductID
        select b.ProductRecipeID
        , b.ComponentProductID
        , b.PricingID
        , dense_rank() over (partition by b.ProductRecipeID order by b.ComponentProductID asc) as prod_rnk
        from base as b
        left join pr_exclude as p on b.ProductRecipeID = p.ProductRecipeID
        where 1=1
        and p.ProductRecipeID is null
    )
--Joining it all together
select a.ProductRecipeID
, a.PricingID as Component1
, b.PricingID as Component2
, c.PricingID as Component3
, d.PricingID as Component4
, e.PricingID as Component5
from final_base as a
left join final_base as b on a.ProductRecipeID = b.ProductRecipeID and b.prod_rnk = 2
left join final_base as c on b.ProductRecipeID = c.ProductRecipeID and c.prod_rnk = 3
left join final_base as d on c.ProductRecipeID = d.ProductRecipeID and d.prod_rnk = 4
left join final_base as e on d.ProductRecipeID = e.ProductRecipeID and e.prod_rnk = 5
where a.prod_rnk = 1
order by 1, 2, 3, 4, 5, 6

Thank you very much. This works perfectly and your explanations are incredible. I appreciate this more than you know. You are amazing! — Jerad Skinner, Dec 22 '18 at 11:18

SQL Server : permutations/combinations without looping

1 Answers1

Linked