Keeping it simple and how to do multiple CTE in a query

You can have multiple CTEs in one query, as well as reuse a CTE:

WITH    cte1 AS        (        SELECT  1 AS id        ),        cte2 AS        (        SELECT  2 AS id        )SELECT  *FROM    cte1UNION ALLSELECT  *FROM    cte2UNION ALLSELECT  *FROM    cte1

Note, however, that SQL Server may reevaluate the CTE each time it is accessed, so if you are using values like RAND(), NEWID() etc., they may change between the CTE calls.

You certainly are able to have multiple CTEs in a single query expression. You just need to separate them with a comma. Here is an example. In the example below, there are two CTEs. One is named CategoryAndNumberOfProducts and the second is named ProductsOverTenDollars.

WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS(   SELECT      CategoryID,      CategoryName,      (SELECT COUNT(1) FROM Products p       WHERE p.CategoryID = c.CategoryID) as NumberOfProducts   FROM Categories c),ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS(   SELECT      ProductID,      CategoryID,      ProductName,      UnitPrice   FROM Products p   WHERE UnitPrice > 10.0)SELECT c.CategoryName, c.NumberOfProducts,      p.ProductName, p.UnitPriceFROM ProductsOverTenDollars p   INNER JOIN CategoryAndNumberOfProducts c ON      p.CategoryID = c.CategoryIDORDER BY ProductName