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

common-table-expression, sql-server, t-sql

Solution

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

WITH    cte1 AS
        (
        SELECT  1 AS id
        ),
        cte2 AS
        (
        SELECT  2 AS id
        )
SELECT  *
FROM    cte1
UNION ALL
SELECT  *
FROM    cte2
UNION ALL
SELECT  *
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.

Problem

I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other related tables. My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in each event. My solution to this is to add a CTE that groups scheduled events and counts the number of participants. This will allow me to join in that information per scheduled event. Keeping the query simple. I like to keep my queries simple, however, If I ever in the future need to have additonal temporary results accessible during my simple query, what do I do? I would really like it, if I could have multiple CTEs but I can't, right? What are my options here? I've ruled out views and doing things at the application data layer. I prefer to isolated my SQL queries.

Original source