RE: Recursive cross join to get all available combinaisons

SSC Guru

Points: 186127

April 9, 2010 at 9:25 am

Ninja's_RGR'us (4/9/2010)
Chris Morris-439714 (4/9/2010)
Hi Ninja
It would be better to change the string list from comma-delimited to fixed position, three character positions per item - that way, the upper limit would be 999 rows. You would still have the benefit of the ceiling figure in the feed row (9 in the example), and the string arithmetic would be far simpler than modifying the existing comma-delimited string to account for numbers of more than one digit.
Work is in progress, mate.
Whatever you say... I'm really clueless when it comes to cte especially with recursion... and I have no time to read up on the subject at the moment.

No worries: -

;WITH calculator AS (

SELECT

ColumnNo= 1,

Number= 1,

Store= CAST(' 1' AS VARCHAR(3000))

UNION ALL

SELECT

ColumnNo= CASE WHEN lr.Number = tr.MaxRows

THEN lr.ColumnNo - 1

ELSE lr.ColumnNo + 1 END,

Number= CASE WHEN lr.Number = tr.MaxRows

-- go back one 3-char column position, increment number found there

THEN CAST(RIGHT(LEFT(lr.Store, (lr.ColumnNo - 1) * 3), 3) AS INT) + 1

ELSE lr.Number + 1 END,

Store= CASE WHEN lr.Number = tr.MaxRows

THEN CAST(

LEFT(lr.Store, (lr.ColumnNo - 2) * 3) -- < chop off last two 3-char column positions

+ RIGHT(' ' + LTRIM(STR( -- < add new Number

CAST(RIGHT(LEFT(lr.Store, (lr.ColumnNo - 1) * 3), 3) AS INT) + 1

)), 3)

AS VARCHAR(3000))

ELSE CAST(lr.Store + RIGHT(' ' + LTRIM(STR(lr.Number + 1)), 3) AS VARCHAR(3000)) END

FROM calculator lr

INNER JOIN (SELECT CAST(16 AS INT) AS MaxRows) tr -- < 16 is the number of shipping rows

ON NOT (lr.ColumnNo = 1 AND lr.Number = tr.MaxRows)

)

SELECT *

FROM calculator

--WHERE ColumnNo > 1 AND ColumnNo < 16 -- < 16 is the number of shipping rows

OPTION (MAXRECURSION 0)

-- Maxrows = 16: (65,535 row(s) affected) / 00:00:02

-- Maxrows = 20: (1,048,575 row(s) affected) / 00:00:46

-- Maxrows = 21: (2,097,151 row(s) affected) / 00:01:33

-- Maxrows = 22: (4,194,303 row(s) affected) / 00:03:08

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden