generate all possible subsets from master set?

Hi.

I have a master set of values, lets say

(1,2,3)

for example. I want to use T-sql to generate all possible subsets of this master set. Order of values is unimportant, what I want is unique sets, i.e.

(1)
(2)
(3)
(1,2)
(1,3)
(2,3)
(1,2,3)

thx.Can I ask why?

In a table or a string?

I'm thinking CROSS JOIN...|||Back up a sec', manster.

Do 1, 2, and 3 represent columns? In that case, you would write a cross-join query as suggested by Brett.

But if 1, 2, 3, ... to N represent values in a single column, and you want all the permutations, you will need to write a stored proc that loops through the dataset N times.

blindman|||thanks, all.

Cross join, most likely. I'm working this up from scratch to complete a current project. The higher-ups are handing me a list of fields from our DB, fifteen max, and then asking for all possible subsets of these fifteen, after which we test the subsets to see which offer the most "value."

if someone can offer a cross join example using numbers 1,2,3 as above, that would be great. I'll just key the number back to the actual field names.

thx.|||That sounds bizzare!

Can you post the DDL of the table?

And some sample data?

And are you're higher ups high?|||yes, I know it sounds bizarre, but you'd have to see the data in the table to understand why they're asking for this, and I can't show it.

bottom line is this: I have a list of 15 fields in a single table and I need to generate all possible subsets of these 15 fields.

table1

F1 F2 F3 etc.

output is all possible subsets of these fields:

F1
F2
F3
F1 F2
F2 F3
F1 F3
F1 F2 F3

thx!|||Have a look at Arnolds Reply...you'd need to marry rows to numbers somehow

You'll also need
CREATE TABLE Numbers(n int)
GO
DECLARE @.x int
SET NOCOUNT ON
SELECT @.x = 1
WHILE @.x < 101 BEGIN
INSERT INTO Numbers (n) SELECT @.x
SELECT @.x = @.x + 1
END
SET NOCOUNT OFF

To play with it...vary cool though...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30752|||What kind of processing power are your higher-ups prepared to pay for?

...because you are looking at something over a QUADRILLION permutations, depending on how many values are in your columns.
Start it running, and then go get yourself a cup of coffee...in Brazil.

blindman|||I'm sorry if I haven't been clear on this. I only need all possible combinations of the FIELD NAMES, not the actual per-record data in the fields.

Once I get all possible combinations of the field names, I can then look at which fields I'll actually use in succeeding queries.

thx|||Originally posted by blindman
What kind of processing power are your higher-ups prepared to pay for?

...because you are looking at something over a QUADRILLION permutations, depending on how many values are in your columns.
Start it running, and then go get yourself a cup of coffee...in Brazil.

blindman

LOL...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30646

Some Calculations here...

Say we have a table of 10,000 measly rows...

You want to join col1 to each of the other 14 columns to represent permutations

OK Col1 + Col2 = 100,000

So i't like Col1 , Row 1 + Col2, Every row

Do that 14 more times for all the other columns...1.4 million rows

And we haven't yet begun! That's still just 2 dimensions of the data

Now lets see, Three dimensions

I guess that would be Col1, Row 1, Col2, Row 1, Col3, All the Rows
Then Col1, Row1, Col2, Row2, Col3 All the rows of data

So what that would be 10,000 * 10,000 * 10,000

Blindman help me out here, sound right?

The for all other cols * 14, or 14 million?

4 Dimensions...

140,000,000? Just a guess...

That about right?|||here's a link to a web page that does what I'm trying to do, but this routine truncates at 500 entries returned...

mine is n=15, k=15

and I'm looking for lex order, list of elements.

http://www.theory.cs.uvic.ca/~cos/gen/comb.html|||Originally posted by manster
I'm sorry if I haven't been clear on this. I only need all possible combinations of the FIELD NAMES, not the actual per-record data in the fields.

Once I get all possible combinations of the field names, I can then look at which fields I'll actually use in succeeding queries.

thx

Was busy doing calcs when you posted...

Are you trying to build a "pick list" of what fields you want to select for an end user..

Even still that's a lot of combinations..its 15 factorial..more actually the way you want it..

DECLARE @.x BIGINT
SELECT @.x = 1*2*3*4*5*6*7*8*9*10*11*12--*13*14*15
SELECT @.x

I get an arithmetic overflow at 13...|||I guess you could call it a pick list as long as all subsets are represented.

I'm looking back at the original field list and see that the main required fields number about 10, so 15 was an over-estimation on my part.

thx.|||But that's only bit data...is that what you're looking for?|||yes, just the subsets taken from the "master" list of fields. I'm only interested in the fieldname subsets, not the records in the db at this point.|||You are still talking about over 4 million permutations.

blindman