How to determine how many combinations!? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to determine how many combinations!?

Author

Topic

mmiller18
Starting Member

6 Posts

Posted - 2012-02-01 : 11:11:44

CREATE TABLE #Cat
(CatID int,
CatName char(50),
FieldID int,
FieldName char(50))

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')

select * from #Cat

Table Results:
CatID CatName FieldID FieldName
1 House 1 Ranch
1 House 2 Split
2 Color 3 Red
2 Color 4 Blue
2 Color 5 Green
2 Color 6 Pink

Looking for all options (in order by category id). I am looking for all combinations that are possible.
I have about 10 other CatIds as well, with multiple options for each one.
In the table example above, CatId 1(House) you can pick either Ranch or Split, but in CatId 2(color) you
can pick ANY of them. you can pick 1-4 selections

Here are typical results I would be looking for from the table that I created above (which is just a sample)

Only by FieldID

13
14
15
16
134
135
136
145
146
156
1345
1346
1356
1456
23
24
25
26
234
235
236
245
246
256
2345
2346
2356
2456

Keep in mind, I have 10 more CatId's with many more field id's, this is where I'm running into
"how do I code this so I don't have to mentally think of the 1,000's of combinations!

Would really appreciate any help i can get on this, I don't even know where to start!!

Marissa

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-02-01 : 11:49:24

I may be being thick here but I don't understand your results. They don't seem to match the sample data you provided.
I would have thought you'd want to see something like:

11 -- because CatID 1 can go with FieldID 1
12 -- because CatID 1 can go with FieldID 2
112 -- because CatID 1 can go with both FieldID1 and FieldID 2
23 -- because CatID 2 can go with FieldID 3
24 --etc
25

Could you clarify please?

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

mmiller18
Starting Member

6 Posts

Posted - 2012-02-01 : 11:54:10

quote:
Originally posted by theboyholty

I may be being thick here but I don't understand your results. They don't seem to match the sample data you provided.
I would have thought you'd want to see something like:

11 -- because CatID 1 can go with FieldID 1
12 -- because CatID 1 can go with FieldID 2
112 -- because CatID 1 can go with both FieldID1 and FieldID 2
23 -- because CatID 2 can go with FieldID 3
24 --etc
25

Could you clarify please?

--------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

Yes, I can clarify that. The results I have posted are only combinations from the FieldID, not including the CatId. The CatID doesn't need to be included in the results, it just there to understand where the category is different. Hopefully that makes sense!

Marissa

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-01 : 12:33:28

I just did a quick Recursive CTE, but I'm not sure it if meets your needs 100%. But, take a look and see if you can use it or not:

;WITH Foo AS
(
    SELECT 
        CatID, CatName, FieldID, FieldName, CAST(FieldID AS VARCHAR(50)) AS FieldString
    FROM 
        #Cat

    UNION ALL

    SELECT 
        C.CatID, C.CatName, C.FieldID, C.FieldName, CAST(FieldString + CAST(C.FieldID AS VARCHAR(50)) AS VARCHAR(50))
    FROM 
        #Cat AS C
    INNER JOIN
        Foo AS F    
        ON C.FieldID > F.FieldID
)

SELECT *
FROM Foo

mmiller18
Starting Member

6 Posts

Posted - 2012-02-01 : 12:49:48

It looks to be on the right track, but every combination will have to have both categories in it. For example, a FieldString of 4 is not possible, it would have to be 14 or 24. Also, should never have a combination of 12 because you can either have 1 or 2, but not both. You can however have multiple of CatID 2.

Marissa

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-02-01 : 12:58:11

This doesn't get the exact output you have listed but I don't know the requirements as well as you. Hopefully, this gets you on the way.[CODE];with cte
as (
select cast(FieldID as varchar(20)) fID, 0 FieldID
--select '.' fID
from #Cat
where CatID = 1

union all

select cast(a.fID + cast(b.FieldID as varchar(20)) as varchar(20)), b.FieldID --+ '.' + cast(b.FieldID as varchar(20))
from cte a
inner join
#Cat b
on b.CatID = 2
and a.FieldID < b.FieldID
)
select fID
from cte
where LEN(fID) > 1
order by left(fID, 1), LEN(fID), fID[/CODE]HTH

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

mmiller18
Starting Member

6 Posts

Posted - 2012-02-01 : 13:02:01

I think that works, now just need to add in the rest of my categories! Only 12 to go :)

Marissa

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-02-01 : 14:20:57

Woo-hoo!!!

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

mmiller18
Starting Member

6 Posts

Posted - 2012-02-01 : 14:40:40

Ok, so I added to more CatId's to my 'fake' table. Not sure how to differentiate between the different types of Categories. Here is my table:

drop table #cat
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

The above code works fine, but lets assume that CatID 1 and CatID 3 can only have 1 selection, but CatId 2 and CatID 4 can have as few or as many selections as possible.

Marissa

mmiller18
Starting Member

6 Posts

Posted - 2012-02-02 : 14:34:43

anyone have any ideas?

Marissa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources