SQL query self-join without CTE or temp

Question

Background

I have a select statement that churns a bit. For sheer simplicity sake, we'll say it looks like this:

select AllTheThings, AndThenSome 
from T1

The output looks like this:

AllTheThings	AndThenSome
Blink	182
Eve	6

I need to essentially duplicate all the rows and add a column for each 'batch' (thankfully always two) as shown below. HOWEVER, this has to be used in Microsoft Dynamics GP Integration Manager, which can't use things like CTE or Temp Tables which would make this easy.

Desired output

Batch	AllTheThings	AndThenSome
First	Blink	182
First	Eve	6
Second	Blink	182
Second	Eve	6

High-churn solution

I know I could do this, but it'll take twice as long:

select 
    'First' as Batch, AllTheThings, AndThenSome 
from 
    T1
union all
select 
    'Second' as Batch, AllTheThings, AndThenSome 
from 
    T1

Other idea

Is there any way to avoid the above solution? I couldn't figure out a way to do a self join on a select query without CTE.

Answer 1

You can join a fixed list of values, that can be declared with values (no CTE nor temp tables):

select b.Batch, t.AllTheThings, t.AndThenSome 
from T1 t
cross join ( values ('First'), ('Second') ) b(Batch)