Home Forums SQL Server 2008 T-SQL (SS2K8) Add variable number of rows into a table based on the values in another table (without cursors/while loops) RE: Add variable number of rows into a table based on the values in another table (without cursors/while loops)

  • March 23, 2013 at 7:40 pm

    #1600094

    Megha P (3/22/2013)

    Hello ,

    you can also try below

    ;WITH CTE AS

    (

    SELECT docid,pages FROM #x

    UNION ALL

    SELECT C.docid,C.pages-1

    FROM #x X INNER JOIN CTE C

    ON X.docid = C.docid

    AND C.pages-1 >0

    )

    SELECT * FROM CTE ORDER BY docid,Pages

    I'll throw in with the others on that. That's what is know as a "Counting rCTE". Another name for it is "Hidden RBAR". Please see the following article for a comparison of rCTEs to 3 other common methods. The rCTE loses on all fronts.

    http://www.sqlservercentral.com/articles/T-SQL/74118/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)