CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqljoin
mkRabbani
mkRabbani

Reputation: 16908

Combine 2 query output in one result set

I have two query output as follow-

Query-1 Output:

A

B

C

Query-2 Output:

1

2

3

4

5

Now I am looking forward to join these two outputs that will return me the following output-

Combine Output:

A | 1

B | 2

C | 3

NULL | 4

NULL | 5

Note: There is no relation between the output of Query 1 & 2

Thanks in advance, mkRabbani

Upvotes: 0

Views: 207

Answers (3)

Aravinth Kannan
Aravinth Kannan

Reputation: 535

You can get the desired result by using Row_Number() and full outer join.

Please check the SQLFiddler, in which I have reproduced the desired result.

http://sqlfiddle.com/#!3/21009/6/0

Upvotes: 1

Meysam Tolouee
Meysam Tolouee

Reputation: 579

Create Table T1 (col1 nVarchar(10))
Go
Create Table T2 (col1 nvarchar(10))
Go
Insert T1 (col1) Values ('A'), ('B'), ('C')
Insert T2 (col1) Values ('1'), ('2'), ('3'), ('4'), ('5')
Go

;With CTE1 As (
Select col1,
    Row_Number() Over(Order By Col1) Rn
    From T1
), CTE2 As (
    Select col1,
        Row_Number() Over(Order By Col1) Rn
        From T2
    )
    Select CTE1.col1, CTE2.col1 From CTE1 Right Outer Join
            CTE2 On CTE1.Rn = CTE2.Rn

Upvotes: 0

martennis
martennis

Reputation: 872

The relation is based on the order of the values from table A and B, so we LEFT JOIN the results from A (containing the numbers) to the results from B (containing the characters) on the ordered index.

DECLARE @a TABLE (col int);
DECLARE @b TABLE (col char(1));
INSERT INTO @a VALUES (1);
INSERT INTO @a VALUES (2);
INSERT INTO @a VALUES (3);
INSERT INTO @a VALUES (4);
INSERT INTO @a VALUES (5);
INSERT INTO @b VALUES ('A');
INSERT INTO @b VALUES ('B');
INSERT INTO @b VALUES ('C');

SELECT B.col, A.col
FROM ( SELECT col, ROW_NUMBER() OVER(ORDER BY col) AS RowNum FROM @a ) AS A
LEFT JOIN ( SELECT col, ROW_NUMBER() OVER(ORDER BY col) AS RowNum FROM @b ) AS B ON A.RowNum = B.RowNum

Upvotes: 2

Related Questions