Generating series, now it’s very simple using recursive CTE

A interesting question asked by one of my Community friend to generate series of 4,8,16…. Without using any kind of looping structure.

This is very simple with the recursive CTE, lets see how we are implementing the solution

; with sam_series (myrow) as (

select 4 as myrow

union all

select myrow+ myrow from sam_series

where myrow<1000

)

select * from sam_series;

 myrow

-----------

4

8

16

32

64

128

256

512

1024

If we dissect the above CTE we can see

The anchor member is:  select 4

Recursive invocation member is: select myrow + myrow which is tied with Union All.

Termination Point is: myRow < 1000.

Conclusion: CTE provides the significant advantage of being able to reference itself, thereby creating a recursive CTE.

Get your cup filled with Tea … Enjoy coding  :)

Post Reference: Vikram Aristocratic Elfin Share