SQL Puzzle | Generate Numbers – Quickly

Do you know, how you can quickly generate 10000 numbers on the fly ?

Please check out the sample input and the expected output below-

Sample Input

Generate Number below 10000

Expected Output

List of number till 10000

Solution 1 – Old Trick (from Joe.Celkos.SQL.For.Smarties.Advanced.SQL.Programming)

--


;WITH SingleDigits(Number) AS 
(
	SELECT Number
	FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
	(9), (0)) AS X(Number)
)
,Series AS 
(
	SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
	from 
	SingleDigits as d1,
	SingleDigits as d2,
	SingleDigits as d3,
	SingleDigits as d4
)
SELECT * FROM Series 

--

Add a comment if you have any other better solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com