Is it possible to Union the result sets of two CTEs with identical structure

Question

I want to union the results sets from the following CTEs so that I get 4 rows of data.

Id  Name
-------------
1    Test1  
2   Test2  
3   Test3  
4   Test4

The Sql I want to use is as follows

;with CTE1 (Id,Name)
as
( 
    select 1 as Id, 'Test1' as Name
    union all
    select 2, 'Test2'
)
select * from CTE1
union all
;with CTE2 (Id,Name)
as
( 
    select 3 as Id, 'Test3' as Name
    union all
    select 4, 'Test4'
)
select * from CTE2

However, I am getting a syntax error suggesting I can not use Union All between the two CTEs. How can I go around this?

Answer 1

with CTE1 (Id,Name)
as
( 
    select 1 as Id, 'Test1' as Name
    union all
    select 2, 'Test2'
),
CTE2 (Id,Name)
as
( 
    select * from CTE1
    union all
    select 3 as Id, 'Test3' as Name
    union all
    select 4, 'Test4'
)
select * from CTE2

or

with CTE1 (Id,Name)
as
( 
    select 1 as Id, 'Test1' as Name
    union all
    select 2, 'Test2'
),
CTE2 (Id,Name)
as
( 
    select 3 as Id, 'Test3' as Name
    union all
    select 4, 'Test4'
)
select * from CTE1
union all
select * from CTE2

Answer 2

YES, but not the way you are doing it. try it this way:

;with CTE1 (Id,Name)
as
( 
    select 1 as Id, 'Test1' as Name
    union all
    select 2, 'Test2'
)
,CTE2 (Id,Name)
as
( 
    select 3 as Id, 'Test3' as Name
    union all
    select 4, 'Test4'
)
select * from CTE1
union all
select * from CTE2

you can have multiple CTEs chained together. There is only one "WITH", and a comma between CTEs. Then in the statement following the CTE, you can reference any of those CTEs.

Answer 3

;with CTE1 (Id,Name) 
as 
(  
    select 1 as Id, 'Test1' as Name 
    union all 
    select 2, 'Test2' 
) 
,CTE2 (Id,Name) 
as 
(  
    select 3 as Id, 'Test3' as Name 
    union all 
    select 4, 'Test4' 
) 
select * from CTE1
union all
select * from CTE2