AdvancedSQLPuzzles/Advanced SQL Puzzles/Recursion Examples/Double the Number Recursion.sql at main · smpetersgithub/AdvancedSQLPuzzles · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
/*----------------------------------------------------
Scott Peters
Double the Number
https://advancedsqlpuzzles.com
Last Updated: 01/23/2026
Microsoft SQL Server T-SQL
*/----------------------------------------------------

---------------------
---------------------
DROP TABLE IF EXISTS #Numbers;
GO

---------------------
---------------------
CREATE TABLE #Numbers
(
Number INTEGER NOT NULL PRIMARY KEY
);

INSERT INTO #Numbers VALUES (1),(2),(3),(4),(5);
GO

---------------------
---------------------
;WITH cte_Numbers AS
(
SELECT  *
FROM    #Numbers
),
cte_Recursion AS
(
SELECT  Number,
        CASE WHEN Number = 1 THEN 1 ELSE Number * 2 END AS RunningSum
FROM    #Numbers
WHERE Number = 1
UNION ALL
SELECT
        t.Number,
        (RunningSum * 2) AS RunningSum
FROM    cte_Recursion cte
        INNER JOIN
        cte_Numbers t ON t.Number = (cte.Number + 1)
)
SELECT   *
FROM     cte_Recursion
ORDER BY Number;
GO