Combine Table Fields

Transact-SQL
1

Hi,

I have two tables (has 1 field in each). I would like to get both fields. There is no relationship to each which means there is no joins, just randomly assigned to each other since the business gave me a set of preset IDs. A cross join returned multiple values. A full outer join is closer but still not what I am looking for:

DROP TABLE IF EXISTS #TEMP1 
CREATE TABLE #TEMP1
(
F1		VARCHAR(5)
)
INSERT INTO #TEMP1 (F1)
VALUES
  ('A')
, ('B')
, ('C')
, ('D')
DROP TABLE IF EXISTS #TEMP2 
CREATE TABLE #TEMP2
(
F2		VARCHAR(5)
)
INSERT INTO #TEMP2 (F2)
VALUES
  ('1')
, ('2')
, ('3')

SELECT * FROM #TEMP1
FULL OUTER JOIN #TEMP2 ON 1=0

I guess I can use a subquery in the field but is that the best way? Ultimately, if I can get this:

image

2

Something like this?

   With temp1
     As (
 Select *
      , rn = row_number() over(Order By @@spid)
   From #TEMP1 t1
        )
      , temp2
     As (
 Select *
      , rn = row_number() over(Order By @@spid)
   From #TEMP2 t2
        )
 Select F1, F2
   From temp1 t1
   Left Join temp2 t2 On t2.rn = t1.rn
  Union 
 Select F1, F2
   From temp2 t2
   Left Join temp1 t1 On t1.rn = t2.rn
1 Like
3

Or - if you really want the NULL values to start at the 'top':

   With temp1
     As (
 Select *
      , rn = row_number() over(Order By @@spid)
      , tr = count(*) over()
   From #TEMP1 t1
        )
      , temp2
     As (
 Select *
      , rn = row_number() over(Order By @@spid)
      , tr = count(*) over()
   From #TEMP2 t2
        )
 Select F1, F2
   From temp1 t1
   Left Join temp2 t2 On (t2.rn + (t1.tr - t2.tr)) = t1.rn
  Union 
 Select F1, F2
   From temp2 t2
   Left Join temp1 t1 On (t1.rn + (t2.tr - t1.tr)) = t2.rn
1 Like
4

Thanks Jeff!