Recursive CTE

A CTE that references itself is called as recursive CTE. Recursive CTE's can be of great help when displaying hierarchical data.



WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE
    WHERE Number <10
 )
SELECT * FROM NumbersCTE