Post reply

Help finding all possible combinations within a group of data

  • February 6, 2012 at 8:35 am

    #267658

    Good morning!

    I have a sample table of data, here is what I am trying to accomplish....

    I need to know how many total possible combinations there are on a form. Each combination must have every Category. Some categories can have 1 or all of the options, others can only have 1 option.

    Sample table

    CREATE TABLE #Cat

    (CatID int,

    CatName char(6),

    FieldID int,

    FieldName char(6))

    Insert into #Cat (CatID,CatName,FieldID,FieldName)

    values (1,'House',1,'Ranch')

    Insert into #Cat (CatID,CatName,FieldID,FieldName)

    values (1,'House',2,'Split')

    Insert into #Cat (CatID,CatName,FieldID,FieldName)

    values (2,'Color',3,'Red')

    Insert into #Cat (CatID,CatName,FieldID,FieldName)

    values (2,'Color',4,'Blue')

    Insert into #Cat (CatID,CatName,FieldID,FieldName)

    values (2,'Color',5,'Green')

    Insert into #Cat (CatID,CatName,FieldID,FieldName)

    values (2,'Color',6,'Pink')

    Insert into #Cat (CatID,CatName,FieldID,FieldName)

    values (3,'Size',7,'Size1')

    Insert into #Cat (CatID,CatName,FieldID,FieldName)

    values (3,'Size',8,'Size2')

    Insert into #Cat (CatID,CatName,FieldID,FieldName)

    values (3,'Size',9,'Size3')

    Insert into #Cat (CatID,CatName,FieldID,FieldName)

    values (4,'Flavor',10,'Cherry')

    Insert into #Cat (CatID,CatName,FieldID,FieldName)

    values (4,'Flavor',11,'Grape')

    Insert into #Cat (CatID,CatName,FieldID,FieldName)

    values (4,'Flavor',12,'Apple')

    Insert into #Cat (CatID,CatName,FieldID,FieldName)

    values (4,'Flavor',13,'Orange')

    Insert into #Cat (CatID,CatName,FieldID,FieldName)

    values (4,'Flavor',14,'Lime')

    select * from #Cat

    Sample Data results:

    CatIDCatNameFieldIDFieldName

    1House 1Ranch

    1House 2Split

    2Color 3Red

    2Color 4Blue

    2Color 5Green

    2Color 6Pink

    3Size 7Size1

    3Size 8Size2

    3Size 9Size3

    4Flavor10Cherry

    4Flavor11Grape

    4Flavor12Apple

    4Flavor13Orange

    4Flavor14Lime

    Ok, so lets assume that CatID 1 and CatID3 can only have 1 option selected. Cat2 and Cat4 can have 1 or more options selected.

    The result combinations I am looking for are based only on the FieldId, so some results I would expect are:

    1/3/7/10

    1/3/8/11

    1/3/4/5/9/10/11/14

    etc.

    Things to note...each CatId must be used. and I need all possible combinations.

    Does anyone know how I could code this? I have about 10 other CatId's to my data table as well, so will add on extra code to whatever solution is found. I am just stuck with how to do it when there are limits to a few of the categories.

    Thoughts/help???

  • February 6, 2012 at 9:34 am

    #1443264

    Don't you just want the sum of counts partitioned by catid?

    If at all possible I would highly recommend normalizing this into 2 tables (Category and Fields).

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 6, 2012 at 9:37 am

    #1443265

    I need results as they show in my first post. So the actual combination of the different possiblities.

  • February 6, 2012 at 9:45 am

    #1443273

    mmiller 85218 (2/6/2012)

    I need results as they show in my first post. So the actual combination of the different possiblities.

    Your earlier post states this:

    Ok, so lets assume that CatID 1 and CatID3 can only have 1 option selected. Cat2 and Cat4 can have 1 or more options selected.

    Where/how in the database are these rules represented?

  • February 6, 2012 at 9:47 am

    #1443275

    They aren't represented in the database, I would just code for it if possible.

  • February 6, 2012 at 9:52 am

    #1443279

    mmiller 85218 (2/6/2012)

    I need results as they show in my first post. So the actual combination of the different possiblities.

    In your original post you listed several different possible results.

    The result combinations I am looking for are based only on the FieldId, so some results I would expect are:

    1/3/7/10

    1/3/8/11

    1/3/4/5/9/10/11/14

    etc.

    I don't understand what these values mean. It seems the description of your desired outcome is not clear to me at least.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 6, 2012 at 9:55 am

    #1443283

    The results are based on the FieldId. So the first example 1/3/7/10 would be

    CatId 1 FieldId 1

    CatId 2 FieldId 3

    CatId 3 FieldId 7

    CatId 4 FieldId 10

    FieldID could be replacedwith FieldName, but figured that the combination would get rather long.

  • February 6, 2012 at 10:00 am

    #1443285

    OK so do you want the number of possibilities or do you want the actual possibilities?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 6, 2012 at 10:02 am

    #1443286

    I need the actual possiblities because I will need to tie the results to actual programs we run and the combinations of all of them.

  • February 6, 2012 at 10:04 am

    #1443288

    One other question, you said

    Some categories can have 1 or all of the options, others can only have 1 option.

    How do you know which ones can only have 1 option?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 6, 2012 at 10:05 am

    #1443290

    mmiller 85218 (2/6/2012)

    I need the actual possiblities because I will need to tie the results to actual programs we run and the combinations of all of them.

    You do know this list can get totally huge very quickly due to the nature of the math you are doing here?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 6, 2012 at 10:06 am

    #1443292

    I just know by the data but for this sample table, I am assuming that CatID 1 and CatID 3 can only have one option, the other 2 can have 1 or more. If I need to add a field that shows how many are allowed, I could do that if it would help with coding.

  • February 6, 2012 at 10:26 am

    #1443304

    mmiller 85218 (2/6/2012)

    I just know by the data but for this sample table, I am assuming that CatID 1 and CatID 3 can only have one option, the other 2 can have 1 or more. If I need to add a field that shows how many are allowed, I could do that if it would help with coding.

    Certainly can't put together some sort of sql that can think. 😉 These rules MUST be in the data if you want to get good data.

    Even so this type of query is quite odd. Not sure how to get every possible combination. The number of results is going to be staggering for this. You have a scaled down sample dataset and the amount of possible combinations is quite large. Just for category 2 alone you have 16 possibilities. Add that to category 1 and you have doubled to 32. Triple that again for category 3. Add in category 4 and you can multiply the 96 rows by 25!!! Yes that is right around 2500 possible combinations if my memory serves me correctly.

    This is very common the math world. What you are looking for is called combinations. Here is a good page with the formula you need to calculate this.

    http://www.mathwords.com/c/combination_formula.htm

    Here is another link with the same formulas but a bit easier to read (at least for me).

    http://www.physicsforums.com/showthread.php?t=161093

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • February 6, 2012 at 10:33 am

    #1443311

    Certainly can't put together some sort of sql that can think

    Come on Sean, don't let the side down, that should be easy for you :w00t:

  • February 6, 2012 at 10:33 am

    #1443312

    I have read up on the combinations code, and will view the links you sent. I know the request is odd, I have told the requestor that there are going to be 100k+ combinations, but they still wanted the number. I know, odd...what reason would anyone need this!?

    drop table #cat

    CREATE TABLE #Cat

    (CatID int,

    CatName char(6),

    OptionsAvail int,

    FieldID int,

    FieldName char(6))

    Insert into #Cat (CatID,CatName,OptionsAvail,FieldID,FieldName)

    values (1,'House',1,1,'Ranch')

    Insert into #Cat (CatID,CatName,OptionsAvail,FieldID,FieldName)

    values (1,'House',1,2,'Split')

    Insert into #Cat (CatID,CatName,OptionsAvail,FieldID,FieldName)

    values (2,'Color',4,3,'Red')

    Insert into #Cat (CatID,CatName,OptionsAvail,FieldID,FieldName)

    values (2,'Color',4,4,'Blue')

    Insert into #Cat (CatID,CatName,OptionsAvail,FieldID,FieldName)

    values (2,'Color',4,5,'Green')

    Insert into #Cat (CatID,CatName,OptionsAvail,FieldID,FieldName)

    values (2,'Color',4,6,'Pink')

    Insert into #Cat (CatID,CatName,OptionsAvail,FieldID,FieldName)

    values (3,'Size',1,7,'Size1')

    Insert into #Cat (CatID,CatName,OptionsAvail,FieldID,FieldName)

    values (3,'Size',1,8,'Size2')

    Insert into #Cat (CatID,CatName,OptionsAvail,FieldID,FieldName)

    values (3,'Size',1,9,'Size3')

    Insert into #Cat (CatID,CatName,OptionsAvail,FieldID,FieldName)

    values (4,'Flavor',5,10,'Cherry')

    Insert into #Cat (CatID,CatName,OptionsAvail,FieldID,FieldName)

    values (4,'Flavor',5,11,'Grape')

    Insert into #Cat (CatID,CatName,OptionsAvail,FieldID,FieldName)

    values (4,'Flavor',5,12,'Apple')

    Insert into #Cat (CatID,CatName,OptionsAvail,FieldID,FieldName)

    values (4,'Flavor',5,13,'Orange')

    Insert into #Cat (CatID,CatName,OptionsAvail,FieldID,FieldName)

    values (4,'Flavor',5,14,'Lime')

    select * from #Cat

    I added the number of options available by CatId, if that would help. I will read the links you mention and see if they will help me understand how to do this!

Viewing 15 posts - 1 through 15 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy