Recursive cross join to get all available combinaisons

• Did you expand the tally table?

; WITH

E1(N) AS ( --=== Create Ten 1's very quickly

SELECT N

FROM (SELECT 1 N0, 1 N1, 1 N2, 1 N3, 1 N4, 1 N5, 1 N6, 1 N7, 1 N8, 1 N9) AS E0

UNPIVOT (N FOR Nx IN (N0, N1, N2, N3, N4, N5, N6, N7, N8, N9)) AS unpvt

), --10

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000

E8(N) AS (SELECT 1 FROM E4 a, E4 b), --100,000,000

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E8)

• Thanks for the code. However it seems to be on the slower side of things. I takes about 47 secs to run with 20 while I have 1 solution than runs under 3 for the same 20 rows.

Keep trying :hehe:.

• Ninja's_RGR'us (4/16/2010)

Thanks for the code. However it seems to be on the slower side of things. I takes about 47 secs to run with 20 while I have 1 solution than runs under 3 for the same 20 rows.

Keep trying :hehe:.

Apparently I didn't refresh my browser... missed alot of the solutions. Glad my thought process was somewhat in the right direction though!

😀

• Paul White NZ (4/10/2010)

Ninja's_RGR'us (4/10/2010)

Wow that's fast... under 3 secs on our prod server.

Did I say prod?, I meant pre-prod :-P.

No seriously it was the prod server, under full rush stress :w00t:.

• isn't that a somewhat heavy laptop ? 😀

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems