Joining unrelated data

Question

I have two tables. Each table has a single column with 2 records. How would I go about merging the columns from both tables into another?

For instance, the first table has records 1 and 2, and the other 4,5. I would like the new table to look like the following.

Answer 1

Here's an option that uses ROW_NUMBER to create a join based on the order of the ID column from each table. Naturally, this assumes an equal number of rows in each table.

--demo setup
Declare @T1 table (id int)
Declare @T2 table (id int)

insert into @T1(id) values(1),(2)
insert into @T2(id) values(4),(5)

--solution
select a.id, b.id from 
(
select id, ROW_NUMBER() over(order by id) as rn from @T1
) a
join 
(
select id, ROW_NUMBER() over(order by id) as rn from @T2
) b
on b.rn = a.rn

---

id  id
1   4
2   5

Answer 2

I'm not sure why you would do this, normally you have a link between tables, so it makes sense what you're joining together.

If you don't have an id on the columns, I guess your best bet would be to give them a rownumber, and use that as an Id.

-- Create table to hold first values
CREATE TABLE #randomfirsttable 
  ( 
     number INT 
  ) 

-- Create table to hold second values
CREATE TABLE #randomsecondtable 
  ( 
     number INT 
  ) 

-- Fill 
INSERT INTO #randomfirsttable 
VALUES      (1), 
            (2), 
            (3), 
            (4), 
            (5)

-- Fill
INSERT INTO #randomsecondtable 
VALUES      (6), 
            (7), 
            (8), 
            (9), 
            (10) 

-- Giving the tables an Id by using row number, you can change the order by by whatever you want to order to give it some meaning.
SELECT FirstTable.number,SecondTable.number 
FROM   (SELECT Row_number() 
                 OVER( 
                   ORDER BY number ASC) Id, 
               number 
        FROM   #randomfirsttable) FirstTable 
INNER JOIN (SELECT Row_number() 
                    OVER( 
                        ORDER BY number ASC) Id, 
                    number 
            FROM   #randomsecondtable) SecondTable 
        ON FirstTable.id = SecondTable.id