CODEWITHSUNDEEP
sqlt-sql
Leigh Bowers
Leigh Bowers

Reputation: 705

SQL: Find all possible combinations/permutations of product option data

I have the following product data (for an online shop):

ProductId  ProductOptionGroupId  ProductOptionId
26         1                     13
26         1                     12
44         1                     22
44         1                     23
44         2                     20
44         2                     21
44         3                     25
44         3                     24

Where a ProductOptionGroup would be (say) "Size" or "Colour", and the ProductOption would be (say) "Large", "Extra Large" and "Red", "Black" etc.

Basically, I want to find all possible product option combinations for each product. For example, for product 44, I'd want:

22, 20, 25  (Large, Black, Cotton)
22, 20, 24  (Large, Black, Nylon)
22, 21, 25  (Large, Red, Cotton)
22, 21, 24  (Large, Red, Nylon)
23, 20, 25  (Extra Large, Black, Cotton)
23, 20, 24  etc...
23, 21, 25
23, 21, 24

Only one product option from each product option group for each row. I.e. Large and Extra large are mutually exclusive.

Ideally, I'd like these values concatenated into a single VARCHAR for each product ("22,21,25" etc).

How can this be achieved in SQL Server 2005?

Thanks

Upvotes: 1

Views: 5095

Answers (3)

Andriy M
Andriy M

Reputation: 77707

WITH
  data (ProductId, ProductOptionGroupId, ProductOptionId) AS (
    /* defining sample data */
    SELECT 26, 1, 13 UNION ALL
    SELECT 26, 1, 12 UNION ALL
    SELECT 44, 1, 22 UNION ALL
    SELECT 44, 1, 23 UNION ALL
    SELECT 44, 2, 20 UNION ALL
    SELECT 44, 2, 21 UNION ALL
    SELECT 44, 3, 25 UNION ALL
    SELECT 44, 3, 24
  ),
  ranked AS (
    /* ranking the group IDs */
    SELECT
      ProductId,
      ProductOptionGroupId,
      ProductOptionId,
      GroupRank = DENSE_RANK() OVER (PARTITION BY ProductId
                                         ORDER BY ProductOptionGroupId)
    FROM data
  ),
  crossjoined AS (
    /* obtaining all possible combinations */
    SELECT
      ProductId,
      GroupRank,
      ProductVariant = CAST(ProductOptionId AS varchar(250))
    FROM ranked
    WHERE GroupRank = 1
    UNION ALL
    SELECT
      r.ProductId,
      r.GroupRank,
      ProductVariant = CAST(c.ProductVariant + ','
        + CAST(r.ProductOptionId AS varchar(10)) AS varchar(250))
    FROM ranked r
      INNER JOIN crossjoined c ON r.ProductId = c.ProductId
                              AND r.GroupRank = c.GroupRank + 1
  ),
  maxranks AS (
    /* getting the maximum group rank value for every product */
    SELECT
      ProductId,
      MaxRank = MAX(GroupRank)
    FROM ranked
    GROUP BY ProductId
  )
/* getting the max ranked combinations for every product */
SELECT c.ProductId, c.ProductVariant
FROM crossjoined c
  INNER JOIN maxranks m ON c.ProductId = m.ProductId
                       AND c.GroupRank = m.MaxRank

Output:

ProductId   ProductVariant
----------- --------------
26          12
26          13
44          22,20,24
44          22,20,25
44          22,21,24
44          22,21,25
44          23,20,24
44          23,20,25
44          23,21,24
44          23,21,25

Useful reading:

Upvotes: 5

Kevin
Kevin

Reputation: 55

The SQL would depend upon your table structure. If the columns are stored in separate tables, then a simple cartesian product (join with no criteria) should yield the desired results.

Upvotes: 0

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56182

Sample:

declare @t table(id int, type1 int, type2 int)

insert @t values(1, 1, 1), (1, 1, 2), (1, 2, 1), (2, 2, 1)

select distinct t1.id, t1.type1, t2.type2
from
(
    select id, type1
    from @t
)t1
full join
(
    select id, type2
    from @t
)t2 on t2.id = t1.id

Output:

id          type1       type2
----------- ----------- -----------
1           1           1
1           1           2
1           2           1
1           2           2
2           2           1

Upvotes: 0

Related Questions