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