How can I have multiple common table expressions in a single SELECT statement?

Question

I am in the process of simplifying a complicated select statement, so thought I would use common table expressions.

Declaring a single cte works fine.

WITH cte1 AS (
    SELECT * from cdr.Location
    )

select * from cte1 

Is it possible to declare and use more than one cte in the same SELECT?

ie this sql gives an error

WITH cte1 as (
    SELECT * from cdr.Location
)

WITH cte2 as (
    SELECT * from cdr.Location
)

select * from cte1    
union     
select * from cte2

the error is

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

NB. I have tried putting semicolons in and get this error

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ';'.

Probably not relevant but this is on SQL 2008.

Solution 1

I think it should be something like:

WITH 
    cte1 as (SELECT * from cdr.Location),
    cte2 as (SELECT * from cdr.Location)
select * from cte1 union select * from cte2

Basically, WITH is just a clause here, and like the other clauses that take lists, "," is the appropriate delimiter.

Solution 2

Above mentioned answer is right:

WITH 
    cte1 as (SELECT * from cdr.Location),
    cte2 as (SELECT * from cdr.Location)
select * from cte1 union select * from cte2

Aditionally, You can also query from cte1 in cte2 :

WITH 
    cte1 as (SELECT * from cdr.Location),
    cte2 as (SELECT * from cte1 where val1 = val2)

select * from cte1 union select * from cte2

val1,val2 are just asumptions for expressions..

Hope this blog will also help : http://iamfixed.blogspot.de/2017/11/common-table-expression-in-sql-with.html

Comments

[Tom Halladay]

That's awesome. I had been filling temp tables with the results of the CTE and combining later, but ran into problems with semi colons when packaging into a stored proc. Nice method!

[Chahk]

Don't forget that cte2 can reference cte1 like so: ... cte2 as (SELECT * FROM cte1 WHERE ...)

[bjpelcdev]

Hero! This has had me stumped for hours

[Dmitry Volkov]

what about declaring recursive and non-recursive expressions?

[Dmitry Volkov]

what about declaring recursive and non-recursive expressions?