1

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.

enter image description here

1
  • 1
    What is your actual use case? Why would the new table be (1, 4), (2, 5) instead of (1, 5), (2, 4), just because of the physical position of the rows?
    – Kayaman
    Aug 23, 2019 at 11:55

2 Answers 2

2

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
0
1

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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.