Generate a table with all possibilities.

Question

Post reply

Generate a table with all possibilities.

ben.brugman

SSChampion

Points: 13369
More actions
June 21, 2013 at 7:23 am

#275462

Hello All,
I need to generate a table.
6 Fields.
(Possible values in all fields are -1, 0, a,b,c,d,e,f)
A field can contain a minus 1,
A field can contain a zero,
A field can contain the next value not yet used. (a,b,c,d,e,f)
A field can contain a value allready used. (a,b,c,d,e)
So the first field can contain -1, 0 or an a.
The second field can contain -1, 0, a or an b (b only if first field is a).
So for a row:
0 a -1 b a x (The x can be a -1, 0, a, b or c)
Not possible
a b a c f x (f is used before d)
With these rules I want to generate all possible rows.
I spend some time on this, but have not managed yet.
Started of with building the table with all combinations and deleting combinations which were not allowed.
At the moment I am thinking of doing something with strings.
(First a should be before the first b etc.).
Haven's solved the problem yet,
Any handy and elegant methods to do this ?
Thanks for your attention,
Ben Brugman

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

maybe like this?

With PossibleValues(ID,Val)

AS

(

SELECT 1,'-1' UNION ALL

SELECT 2,'0' UNION ALL

SELECT 3,'a' UNION ALL

SELECT 4,'b' UNION ALL

SELECT 5,'c' UNION ALL

SELECT 6,'d' UNION ALL

SELECT 7,'e' UNION ALL

SELECT 8,'f'

),ColumnLimits(ID,ColumnName)

AS

(

SELECT 1,'Column1' UNION ALL

SELECT 2,'Column2' UNION ALL

SELECT 3,'Column3' UNION ALL

SELECT 4,'Column4' UNION ALL

SELECT 5,'Column5' UNION ALL

SELECT 6,'Column6'

)

SELECT *

FROM PossibleValues

CROSS APPLY ColumnLimits

WHERE PossibleValues.ID >= ColumnLimits.ID

ORDER BY

ColumnLimits.ColumnName,

ColumnLimits.ID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

The best way to present such question would be with bigger example of required output.

If I understood you correctly your final table should have 6 columns...

So, may be that:

;WITH vals

AS

(

SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN V = '-1' THEN '' ELSE V END ) - 2 RN, V

FROM (VALUES ( '-1'),('0'),('a'),('b'),('c'),('d'),('e'),('f')) Q(V)

)

SELECT V1.V AS Col1

,V2.V AS Col2

,V3.V AS Col3

,V4.V AS Col4

,V5.V AS Col5

,V6.V AS Col6

FROM (SELECT * FROM vals WHERE RN<=1) V1

CROSS JOIN (SELECT * FROM vals WHERE RN<=2) V2

CROSS JOIN (SELECT * FROM vals WHERE RN<=3) V3

CROSS JOIN (SELECT * FROM vals WHERE RN<=4) V4

CROSS JOIN (SELECT * FROM vals WHERE RN<=5) V5

CROSS JOIN (SELECT * FROM vals WHERE RN<=6) V6

WHERE (V2.RN < 2 OR V2.RN = V1.RN + 1)

AND (V3.RN < 3 OR V3.RN = V2.RN + 1)

AND (V4.RN < 4 OR V4.RN = V3.RN + 1)

AND (V5.RN < 5 OR V5.RN = V4.RN + 1)

AND (V6.RN < 5 OR V6.RN = V5.RN + 1)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ben.brugman SSChampion Points: 13369 More actions · Answer 3

Eugene Elutin (6/21/2013)
The best way to present such question would be with bigger example of required output.
If I understood you correctly your final table should have 6 columns...
So, may be that:

Part of your generated table:

0-1b0ba (Not correct b used before a)

a-10-1-1-1

-10aabd (Not correct d used before c)

-10bcdc (Not correct b used before a)

a0a-1b0

a0acdd

So there are still some rows to many.

But formulating the question did help me, to come up with a solution, using the strings as mentioned.

So thank you all for the that I could ask the question.

drop table xx

;

WITH

S AS (SELECT '-1' X UNION SELECT '0' UNION SELECT 'A' UNION SELECT 'B' UNION SELECT 'C'

UNION SELECT 'D' UNION SELECT 'E' UNION SELECT 'F'),

r1 as (select S1.X A, S2.X B, S3.X C, S4.X D, S5.X E, S6.X F from S S1,S S2,S S3,S S4,S S5,S S6),

r2 as (select *, A+B+C+D+E+F TTT FROM r1)

select * INTO XX from r2

DELETE xx WHERE

PATINDEX ( '%A%' , ttt+'ABCDEF' ) > PATINDEX ( '%B%' , ttt+'ABCDEF' )

or

PATINDEX ( '%B%' , ttt+'ABCDEF' ) > PATINDEX ( '%C%' , ttt+'ABCDEF' )

or

PATINDEX ( '%C%' , ttt+'ABCDEF' ) > PATINDEX ( '%D%' , ttt+'ABCDEF' )

or

PATINDEX ( '%D%' , ttt+'ABCDEF' ) > PATINDEX ( '%E%' , ttt+'ABCDEF' )

or

PATINDEX ( '%E%' , ttt+'ABCDEF' ) > PATINDEX ( '%F%' , ttt+'ABCDEF' )

Formulating the question helped me into thinking of the strings solution. Where before I was treating all columns as individual columns.

Key of the code is the first occurence of an 'A' should be before the first occurence of a 'B' etc.

Sorry that I didn't come up with a larger example list.

A 'fairly' random selection of rows generated by the above:

--

-- Pick up some 'random' rows from the generated table.

--

SELECT TOP 20 '--' [--], A,B,C,D,E,F FROM (

select A,B,C,D,E,F,

ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY (SELECT NULL)) R from xx

) AS y WHERE R%351 = 13

--ABCDEF

---1-1-1-10A

--A-1A0-1A

--00-10-1-1

---10A0BA

--0A-1000

--0A0BA-1

--AAB-1CD

--AB-1-10-1

--ABAACA

--ABCBDA

Thanks,

Ben