SQL to combine columns for two tables with same number of rows

Question

I have two tables which I want to combine. They each contain the same number of rows, and I want my result to have the same number of rows but the columns from both. It doesn't matter which row from table A is matched to which row from table B. The reason for this is that the query I'm writing will link the rows, so at the moment there is no link between them.

My current approach (SQL below) is to select the data from each table with a row number, then join the tables based on that row number. This gives the result I want, but is there an easier way to do it?

SELECT IdA, IdB
FROM (
    SELECT IdA, ROW_NUMBER() OVER(ORDER BY IdA) RowNum 
    FROM TableA
) a
JOIN (
    SELECT IdB, ROW_NUMBER() OVER(ORDER BY IdB) RowNum 
    FROM TableB
) b
ON a.RowNum = b.RowNum;

If you managed to get to the result, why are questioning it? Is it too expensive in terms of performance? — cdrrr, Sep 23 '19 at 11:11
It's just a nuisance to write, as I'm having to do it quite a few times. It felt like something that might have a standard approach (something analogous to joins) so I wanted to check I wasn't missing anything. — Tim, Sep 23 '19 at 11:18
@Tim - What do you mean by "[...] I'm having to do it quite a few times"? — cdrrr, Sep 23 '19 at 11:20
I mean writing it several times. This is a small part of a much larger piece of SQL work, and this requirement keeps coming up with different sets of data. I get the impression from these comments that there is no obvious alternative that I'm missing, which is fine. But if I could have done something simpler (e.g. TableA COLUMNJOIN TableB) then it would save me the effort of writing verbose SQL unnecessarily. — Tim, Sep 23 '19 at 11:23

cdrrr · Accepted Answer · 2019-09-23T16:17:02.487

I found an example (possible solution) here and I extracted the solution for an easy reference:

CREATE TABLE #Temp_One (
    [RowNum] [int] IDENTITY (1, 1) NOT NULL ,
    [Description] [nvarchar] (50) NOT NULL
)

CREATE TABLE #Temp_Two (
    [RowNum] [int] IDENTITY (1, 1) NOT NULL ,
    [Description] [nvarchar] (50) NOT NULL
)

INSERT INTO #Temp_One
SELECT Your_Column FROM Your_Table_One ORDER BY Whatever

INSERT INTO #Temp_Two
SELECT Your_Column FROM Your_Table_Two ORDER BY Whatever

SELECT * 
FROM #Temp_One a 
    LEFT OUTER JOIN #Temp_Two b 
         On a.RowNum = b.RowNum

In order to not repeat yourself writing it again and again, you could create a STORED PROCEDURE or a VIEW and call it each time you need from a 3rd party application. In this way, you make sure you respect the DRY concept:

  CREATE STORED PROCEDURE MyProc
    AS
    BEGIN
    --you could insert a TRY CATCH block -- not mandatory
        CREATE TABLE #Temp_One (
        [RowNum] [int] IDENTITY (1, 1) NOT NULL ,
        [Description] [nvarchar] (50) NOT NULL
        )

        CREATE TABLE #Temp_Two (
        [RowNum] [int] IDENTITY (1, 1) NOT NULL ,
        [Description] [nvarchar] (50) NOT NULL
        )

        INSERT INTO #Temp_One
        SELECT Your_Column FROM Your_Table_One ORDER BY Whatever

        INSERT INTO #Temp_Two
        SELECT Your_Column FROM Your_Table_Two ORDER BY Whatever

        SELECT * 
        FROM #Temp_One a 
        LEFT OUTER JOIN #Temp_Two b 
             On a.RowNum = b.RowNum
    END
    GO

OR

CREATE VIEW MyView
AS

SELECT ....

GO

SQL to combine columns for two tables with same number of rows

1 Answers1