Hello,

I have a piece of code which goes like this ---

A]

with cte1 AS (

), cte2 as (

),

select a,b,c

B]

with cte3 AS (

), cte4 as (

),

select x,y,z.

I need a way to union these 2 result sets. I can't write all 4 concurrently and then select as I would need to uniquely identify elements in each SET A and SET B. One approach i see is to put result set of each in a temp table and then finally do union all with temp tables. Would like suggestions or any other better way. As i have aboout 8 of such similar individual sets.

Thanks

One method is to insert the result of both into a table.... and then perform select statement.

 I can't write all 4 concurrently and then select as I would need to uniquely identify elements in each SET A and SET B.

Incorrect.  You can do this, you just assumed it was impossible.  To go a bit further, you would still have the same problem if you generated the resultsets completely separate from each other - something that would be more obvious perhaps if you had attempted to demonstrate your final, desired output. 

with cte1 as (),
cte2 as (),
cte3 as (),
cte4 as (),
select ..., 'A' as [set] from cte1 join cte2 on ...
union all
select ..., 'B' from cte3 join cte4 on ...
order by ...;

• Proposed as answer by VSPatel Wednesday, January 28, 2015 2:56 PM
• Marked as answer by Kalman TothModerator Sunday, February 08, 2015 6:18 PM

Option 1: Temp tables

1. create tables
create table #t1 (...)...
create table #tn (..)

2. Populate them
insert into #t1
select a, b ..from tablea ...where..

insert into #tn
select a, b ..from tablen ...where..

3. Perform DML like union sets etc.,

Option 2: Derived tables

select col1, col2..coln  
from 
(
select a, b, ..n
from tablea where...

) join 
(
select a, b, ..n
from tablen where...
)
 on ... 
union all
select col1, col2..coln  
from 
(
select a, b, ..n
from tableb where...

) join 
(
select a, b, ..n
from tablen where...
)
 on ... 

Hi, CTEs would be very helpful and convenient wherein you want to use nested like table usage ie., declare result set in CTE and wanted to form other cte by using previously declared CTE etc.,

In your example it is not so then you can have different options... form derive tables or load data into temp tables and use them. Please take a look.

Since you can't write all 4 concurrently then try to union directly to your queries as below,

Select	a, b, c
From
(
[Query From cte1] join [Query From cte2)
)

Union

Select	x, y, z
From
(
[Query From cte3] join [Query From cte4)
)

Thanks.Works Perfectly... :)