Galin Iliev's blog

Software Architecture & Development

T-SQL Number Generator

3. November 2010 Galcho SQL, Tip&Tricks (2)

Here is some more T-SQL fun: number generator Smile I found this code in comments of this blog post and it is so cools so I decided to save it here for reference:

-- use number generation
WITH Numbers3( n ) AS ( SELECT 1 UNION SELECT 0 ),
     Numbers2( n ) AS ( SELECT 1 FROM Numbers3 n1 CROSS JOIN Numbers3 n2 ),
     Numbers1( n ) AS ( SELECT 1 FROM Numbers2 n1 CROSS JOIN Numbers2 n2 ),
     Numbers0( n ) AS ( SELECT 1 FROM Numbers1 n1 CROSS JOIN Numbers1 n2 ),
     NumbersBase ( n ) AS ( SELECT 1 FROM Numbers0 n1 CROSS JOIN Numbers0 n2 ),
     Numbers ( Number ) AS 
     ( 
         SELECT n
         FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
         FROM NumbersBase ) D ( n )
         WHERE n <= 50
      )
      
SELECT * FROM Numbers
Tags :

Comments (2) -

  • Anonymous

    1/26/2011 2:24:15 PM | Reply

    ;WITH CTE AS
    (  
      SELECT
        1 AS n
      UNION ALL
      SELECT
        n + 1
      FROM
        CTE
      WHERE
        n < 50
    )
    SELECT
      *
    FROM
      CTE

  • newguide

    4/28/2011 6:19:09 AM | Reply

    Upiq.com actually finds much cheaper flights & hotels prices than Momondo because Upiq compares much more travel sites than Momondo. You can check it out at http://www.upiq.com

Add comment

Loading