Get all possible concatenations

Question

I have a database that I've inherited and need to create a query that generates all possible variations of a SKU. One table has the "base" SKU and the other table has all the SKU modifiers.

Example

Base SKU: MARIN could be modified into

MARINR15 MARINB15 MARING15 MARINR17 MARINB17 MARING17 MARINR19 MARINB19 MARING19 MARINR20 MARINB20 MARING20

Base SKU

ProductID   SKU
----------- ---------------
532         MARIN

SKU Modifiers

ProductID   OptionName           OptionValue              SkuModifier
----------- -------------------- ------------------------ -----------
532         Color                Red                      R
532         Color                Green                    G
532         Color                Blue                     B
532         Size                 17"                      17
532         Size                 15"                      15
532         Size                 19"                      19
532         Size                 20"                      20

This isn't generic enough, or your question is lacking clarity. You appear to be adding the first character of `OptionValue` where `OptionName` is Colour but adding the numeric part of `OptionValue` if `OptionName` is Size. What other `OptionName`s are you going to have, and how are they treated? — El Ronnoco, Nov 01 '11 at 16:30
Sorry guys, it seems that the formatting was off, and the "SkuModifier" column was off the screen. The values come from "SkuModifier" column. — Bryan, Nov 01 '11 at 16:37
@Bryan - Do you have (potentially) more than 2 sets of modifiers - or just modifiers different than those listed here? And how do you determine the order for the modifiers (concatenation order)? — Clockwork-Muse, Nov 01 '11 at 16:58
@X-Zero There is a possibility of there being 1 or more sets of modifiers. Order isn't a big deal if all the possible combinations are there, because I have another table which with data from our warehouse which contains all the modified SKUs, but no ProductID. — Bryan, Nov 01 '11 at 18:50
@Bryan - I guess not having `order` makes sense, because some products may not need a particular modifier (... how do you want those handled?). Just be aware that you will end up with a large intermediate set. But if that _is_ the case... what determines the 'canonical' ordering (to keep two SKUs with the same modifiers, but different orders) to use when getting the SKU? — Clockwork-Muse, Nov 01 '11 at 19:42
Will order not make a difference? How will your system cope with `MARIN17R` rather than `MARINR17`? — El Ronnoco, Nov 02 '11 at 10:12
@El Ronnoco - If I have all possibilities than I can filter out using another dataset we have from our warehouse. The problem is that our warehouse doesn't know anything about ProductID. If I am able to assemble all possibilities than I can get all Skus and ProductIDs — Bryan, Nov 02 '11 at 13:51
@X-Zero - The large dataset isn't an issue. Ordering also is not an issue as long as we can get all possibilities. — Bryan, Nov 02 '11 at 13:53
@Bryan So really the example list above is not sufficient. You will require all possible permutations of modifiers eg `MARIN17B` and `MARINB17`? — El Ronnoco, Nov 02 '11 at 14:07
@ElRonnoco - Correct. If we have all permutations then the order (which our e-commerce applications decide) shouldn't matter because I have a complete list of SKUs from our warehouse but the warehouse isn't aware of ProductIDs. — Bryan, Nov 02 '11 at 16:19
Sorry everyone. I should have been much more specific in my original posting. I will make sure to be more specific in the future! — Bryan, Nov 02 '11 at 16:20

score 3 · Answer 1 · answered Nov 01 '11 at 16:53

DROP TABLE #Base
DROP TABLE #Modifiers

CREATE TABLE #Base
(
    ProductId int,
    SKU varchar(32)
)

CREATE TABLE #Modifiers
(
    ProductId int,
    OptionName varchar(32),
    OptionValue varchar(32),
    SKUModifier varchar(32)
)

INSERT INTO #Base
SELECT 532, 'MARIN'

INSERT INTO #Modifiers
SELECT 532, 'Color', 'Red', 'R' UNION ALL
SELECT 532, 'Color', 'Green', 'G' UNION ALL
SELECT 532, 'Color', 'Blue', 'B' UNION ALL
SELECT 532, 'Size', '17"', '17' UNION ALL
SELECT 532, 'Size', '15"', '15' UNION ALL
SELECT 532, 'Size', '19"', '19' UNION ALL
SELECT 532, 'Size', '20"', '20'

SELECT B.SKU + M.SKUModifier + M2.SKUModifier FROM #Base B
    JOIN #Modifiers M ON B.ProductId = M.ProductId AND M.OptionName = 'Color'
    JOIN #Modifiers M2 ON B.ProductId = M2.ProductId AND M2.OptionName = 'Size'

Results:

MARINR17
MARING17
MARINB17
MARINR15
MARING15
MARINB15
MARINR19
MARING19
MARINB19
MARINR20
MARING20
MARINB20

Stefan Mai · Answer 2 · 2011-11-01T16:53:41.077

0

SELECT 
  base.sku+color.SkuModifier+size.SkuModifier 
FROM base 
INNER JOIN modifiers as color ON color.OptionName = 'Color'
INNER JOIN modifiers as size ON size.OptionName = 'Size'

You'll probably have to process OptionValue (for instance, removing the " from Size and taking the first letter from Color), but this will get you on the right path.

EDIT -- Thanks for the clarification, I've updated the SQL.

edited Nov 01 '11 at 16:53

answered Nov 01 '11 at 16:31

Stefan Mai

23,367
6
55
61

Seems all right, but maybe `base.ProductID = color.ProductID` and likewise for `size` should be added to the join conditions. What do you think? – Andriy M Nov 01 '11 at 16:58

Clockwork-Muse · Accepted Answer · 2011-11-03T15:35:34.080

You can use a recursive solution (and indeed, this is probably the only viable answer). You'd probably save processing if you had a predifined ordering (because at the moment the only way I can think to do this is text concatenation).

Here is a general solution that should get you your needed results.
Note that this was written and run on DB2 (iSeries) - you may need to adjust it for SQL Server.

WITH Combined(productId, options, combination, level) as (
              SELECT productId, optionName, skuModifier, 1
              FROM #Modifiers
              UNION ALL
              SELECT a.productId, a.options || b.optionName,
                     a.combination || b.skuModifier, a.level + 1
              FROM Combined as a
              JOIN #Modifiers as b
              ON b.productId = a.productId
              AND a.options not like ('%' || b.optionName || '%')),
     Option_Count(productId, count) as (SELECT productId, COUNT(DISTINCT optionName)
                                        FROM #Modifiers
                                        GROUP BY productId)
SELECT a.sku || COALESCE(b.combination, '')
FROM #Base as a
LEFT JOIN (Combined as b
           JOIN Option_Count as c
           ON c.productId = b.productId
           AND c.count = b.level)
ON b.productId = a.productId)

Which yields:

MARIN17R        
MARIN15R        
MARIN19R        
MARIN20R        
MARIN17G        
MARIN15G        
MARIN19G        
MARIN20G        
MARIN17B        
MARIN15B        
MARIN19B        
MARIN20B        
MARINR17        
MARING17        
MARINB17        
MARINR15        
MARING15        
MARINB15        
MARINR19        
MARING19   
MARINB19   
MARINR20   
MARING20   
MARINB20

Personally, though, I think I'd try to get some sort of ordering established - this would at least allow you to knock out dealing with optionName (although in that case you may want to further normalize the tables).
Please note that the CTE Option_Count is being used to restrict results to 'full-length' combinations - permutations where all the options are used, rather than just some of them.

Is this example specific to some version of SQL Server? I cannot seem to get it to run. I'm on SQL Server 2005. I figure I need to replace all the "||" with +. After that I get errors about "types don't match between the anchor and recursive part in column "options", and one for column "combination" as well — Bryan, Nov 03 '11 at 13:30
Ah, sorry, this runs perfectly well on DB2. I thought the `||` was universal for string concatenation on SQL, I guess not (DB2 doesn't use `+` - do you have the `CONCAT()` function? I don't know why you're getting a type-mismatch error for the recursive query - the CTE _should_ be inheriting the type from the selected column. Perhaps you need to wrap it in a manual cast to `varchar` or something? — Clockwork-Muse, Nov 03 '11 at 15:34

Get all possible concatenations

3 Answers3