generate number series using recursive cte in sql

A Recursive CTE (Common Table Expression) is a CTE that references itself.  In doing so, the initial CTE is repeatedly executed, returning subsets of data, until it returns the complete result set.