Looking for examples of CTE and recursion

I was looking for some example of real uses of CTE and recursion in MS SQL - I guess this started with MS SQL 2008...

Thanks!

Actually, it started with SQL 2005 :) and since I discovered it, I've been using it in most of my queries every day!!

Besides that you can do recursive queries, it also makes it easy to organize your code.

For example:
Your normal SQL (pseudocode)

Code:

---

SELECT *
FROM (SELECT * FROM table_a WHERE a = 5) AS Q1
  INNER JOIN (SELECT * FROM table_a WHERE a = 5) AS Q2 ON Q1.something = Q2.something_else

---

Imagine previous query mixed with other sub-queries, and many other things...

As you notice this: "(SELECT * FROM table_a WHERE a = 5)" is repeating twice! some may make a view out of that query.

Now you can write the same thing, much more clear to understand, like:

Code:

---

; WITH
Q AS (
    SELECT * FROM table_a WHERE a = 5
)
SELECT *
FROM Q AS Q1
  INNER JOIN Q AS Q2 ON Q1.something = Q2.something_else

---

So now "Q" query is like a view, and you can use it as many times as you want, instead of creating an actual view.

Another thing that WITH recursion is VERY useful, is cases where you have parent / child records. So you can "crawl" on the "tree" recursively until you find the grand-grand-parent etc....

A good example for that, see post #20 here: http://www.vbforums.com/showthread.php?t=670242

I'll try to find more examples.

[Edit]
Here is another good one: SQL Server 2005 - Combinations

And another one: SQL Server 2005 - Remove repeating spaces from string using CTE

This one generates a range of numbers: http://www.vbforums.com/showthread.php?t=670346

Making the query easier to read: http://www.vbforums.com/showthread.php?t=669010

Another reason to use WITH, is that it makes it easy to debug your SQL:

Code:

---

; WITH
aa AS (
        SELECT 0 AS Num
       
        UNION ALL
       
        SELECT Num + 2 FROM aa WHERE Num < 100
)
, bb AS (
        SELECT 1 AS Num
       
        UNION ALL
       
        SELECT Num + 3 FROM bb WHERE Num < 100
)
, cc AS (
        SELECT aa.Num
        FROM aa
                INNER JOIN bb ON aa.Num = bb.Num
)
--SELECT * FROM aa
--SELECT * FROM bb
SELECT * FROM cc

---

If you comment out "SELECT * FROM cc", and uncomment "... from aa", then you can see what "aa" returns, then you can check what "bb" returns, and so on.

This way you can verify if each sub-query is doing what it is supposed to do