Recursive CTE to generate all combinations sub groups within a group

Question

I am trying produce an aggregated result for all possible combinations of rows linked by one column (package_id) but where another column (package_item_id) does not match.

I have a derived table packages:

package_id	package_item_id	quantity	variant_id	retail_price	sell_price	stock_available
1000001	28	1	3399	489.00	342.30	26
1000001	28	1	3400	489.00	342.30	23
1000001	28	1	3409	489.00	342.30	81
1000001	29	1	821	44.95	35.95	741
1000001	30	1	807	39.95	31.95	162
1000002	31	1	6369	519.00	363.30	93
1000002	31	1	6371	519.00	363.30	60
1000002	31	1	6372	519.00	363.30	40
1000002	31	1	19289	519.00	363.30	24
1000002	31	1	19290	519.00	363.30	20
1000002	31	1	21774	519.00	363.30	14
1000002	31	1	23045	519.00	363.30	0
1000002	37	1	821	44.95	35.95	741
1000002	38	1	807	39.95	31.95	162

This structure supports a package deal system for a retail website:

package_id relates to a group of elements that can be purchased as package.

package_item_id breaks down the sub elements (of varying number depending on the package) where the user may have a choice of variant_ids to select from.

i.e. Package: Premium Mountain Bike, Package Item: Frame, Variants: Blue,Red,Yellow, Package Item: Wheels, Variants: Narrow, Wide, Package Item: Saddle, Variants: Standard

I am trying to produce a result which aggregates every potential combination for a package_id. Concatenating the package_id & variants_ids into a querystring and calculating the total prices for grouped items. The quantity column determines the amount supplied for package_item_id so is a multiplier for the prices. Furthermore I would like to check there is sufficient stock of each variant to make up the package true (1) or false (0)

An example of the result I am trying to achieve for just the first package_id:

package_id	querystring	retail_price	sell_price	in_stock
1000001	p_id=1000001&v_id=3399&v_id=819&v_id=807	598.9	430.2	1
1000001	p_id=1000001&v_id=3400&v_id=819&v_id=807	598.9	430.2	1
1000001	p_id=1000001&v_id=3409&v_id=819&v_id=807	598.9	430.2	1
1000001	p_id=1000001&v_id=3399&v_id=821&v_id=807	573.9	410.2	1
1000001	p_id=1000001&v_id=3400&v_id=821&v_id=807	573.9	410.2	1
1000001	p_id=1000001&v_id=3409&v_id=821&v_id=807	573.9	410.2	1

I have been attempting to use window functions in combination recursive CTE to create all different combinations under combination ID which I was hoping to them group to get the aggerated result I need but so far I can't achieve what I am aiming to do.

WITH RECURSIVE cte_packages AS (
SELECT 
  package_id,
  DENSE_RANK() OVER (ORDER BY package_id) AS p_group,
  package_item_id,
  DENSE_RANK() OVER (PARTITION BY package_id ORDER BY package_item_id) AS pi_group,
  ROW_NUMBER() OVER (PARTITION BY package_item_id ORDER BY variant_id) AS pi_group_row,
  COUNT(*) OVER (PARTITION BY package_item_id) AS pi_group_row_count,
  quantity,
  variant_id,
  retail_price,
  sell_price,
  stock_available
FROM packages
),
cte_combinations AS
(
  SELECT 
        0 AS combination_id,
        p_group,
        p_group AS next_p_group,
        pi_group,
        pi_group_row,
        CASE 
          WHEN pi_group_row < pi_group_row_count
          THEN pi_group_row + 1
          ELSE 1
        END
        AS next_pi_group_row,
        pi_group_row_count,
        package_id,
        package_item_id,
        quantity,
        variant_id,
        retail_price,
        sell_price,
        stock_available     
    FROM 
        cte_packages
    WHERE 
        p_group = 1 AND pi_group = 1 AND pi_group_row = 1
    UNION
    SELECT
        cc.combination_id + 1,
        cpi.p_group,
        CASE 
          WHEN cpi.pi_group_row = cpi.pi_group_row_count
          THEN cpi.p_group + 1
          ELSE cpi.p_group
        END
        AS next_p_group,
        cpi.pi_group,
        cpi.pi_group_row,
        CASE 
          WHEN cpi.pi_group_row < cpi.pi_group_row_count
          THEN cpi.pi_group_row + 1
          ELSE 1
        END
        AS next_pi_group_row,
        cpi.pi_group_row_count,
        cpi.package_id,
        cpi.package_item_id,
        cpi.quantity,
        cpi.variant_id,
        cpi.retail_price,
        cpi.sell_price,
        cpi.stock_available
    FROM
        cte_combinations cc
    INNER JOIN
        cte_packages cpi
        ON cpi.p_group = cc.next_p_group AND cpi.pi_group = 1 AND cpi.pi_group_row = cc.next_pi_group_row
    LEFT JOIN
        cte_packages opi
        ON opi.p_group = cpi.p_group AND opi.pi_group <> 1
)
SELECT * FROM cte_combinations

I am not sure if I am approaching this correctly or is what I'm trying to achieve is possible.

DB Fiddle link below:

https://dbfiddle.uk/hEXZa674

Answer 1

AFAICT we need to generate all possible combinations of variant_id for each package_id. Since the number of package_item_id varies, this will involve a self-join for each package_item_id. However, with varying numbers of items, one would typically need a dynamic number of joins, which SQL does not natively support.

This snippet would probably achieve the result you're looking for but only for three package_item_id values per package_id

SELECT 
  p1.package_id,
  CONCAT('p_id=', p1.package_id, '&v_id=', p1.variant_id, 
         '&v_id=', p2.variant_id, '&v_id=', p3.variant_id) AS querystring,
  (p1.quantity * p1.retail_price + p2.quantity * p2.retail_price + p3.quantity * p3.retail_price) AS total_retail_price,
  (p1.quantity * p1.sell_price + p2.quantity * p2.sell_price + p3.quantity * p3.sell_price) AS total_sell_price,
  CASE 
    WHEN p1.stock_available >= p1.quantity AND p2.stock_available >= p2.quantity AND p3.stock_available >= p3.quantity 
    THEN 1 
    ELSE 0 
  END AS stock_available
FROM packages p1
JOIN packages p2 ON p1.package_id = p2.package_id AND p1.package_item_id <> p2.package_item_id
JOIN packages p3 ON p1.package_id = p3.package_id AND p1.package_item_id <> p3.package_item_id AND p2.package_item_id <> p3.package_item_id
GROUP BY p1.package_id, p1.variant_id, p2.variant_id, p3.variant_id;