sqlsql-servercommon-table-expressionlateral-joindynamics-gp

SQL query self-join without CTE or temp


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.


Solution

  • 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)