Home Forums SQL Server 2008 T-SQL (SS2K8) Recursive cross join to get all available combinaisons RE: Recursive cross join to get all available combinaisons

  • ChrisM@Work

    SSC Guru

    Points: 186043

    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

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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