The original Oracle query was recursive, and the CTE, or Common Table Expression, is the way that SQL Server handles recursion. In SQL Server, a recursive CTE has to have two elements. First, an "anchor" query, which is then added to via UNION or UNION ALL with a query that is self-referencing, by introducing a table reference to the CTE using it's alias as if it were a table name. I can't explain all the internals, but it makes for rather easy ways of dealing with hierarchical structures or even just a simple "tally" table (a table of just numbers you can use for generating things like a list of dates (calendar, anyone?). Does that help?

I'd also recommend reading some Itzhik Ben-Gan's books on T-SQL Querying and T-SQL Programming. He covers recursion and does a great job of explaining it.