add two different queries result into one table

Question

I have two different query (having same no. of columns in result). I want to put both in one table.

for example i have following table:

id     country     salary 
1        us        10000

2        uk        25000

3        us        35000

4        uk        31000

5        uk        26000

now I have following queries:

Query 1 :

select * from table where country='us';

and

Query 2 :

select * from table where country='uk';

i have one final table having six columns like:

 id1   |country1  |  salary 1 |  id2  |  country2 |  salary2

Now, i want to put both queries result in this table so following output should be shown:

Desired Output:

id1   |country1  |  salary 1  |  id2   |  country2 |  salary2 
 1    |     us   |   10000    |    2   |      uk   |   25000

 3    |     us   |   35000    |    4   |      uk   |   31000

null  |   null   |   null     |    5   |      uk   |   26000

I have tried this but it doesn't combine the result:

insert into table (id1,country1,salary1)
select id,country,salary
from table1
where country='us';

and

insert into table (id2,country2,salary2)
select id,country,salary
from table1
where country='uk';

but it gives following result:

id1   |country1    |  salary 1  |  id2      |  country2   |  salary2 
 1    |     us     |     10000  |     null  |     null    |     null

3     |    us      |    35000   |    null   |    null     |    null

null  |   null     |   null     |   2       |    uk       |    25000

null  |   null     |   null     |   4       |    uk       |    31000

null  |   null     |   null     |   5       |    uk       |    26000

Please help me out:

You can use joins to add them into one insert statement instead of 2 insert statements — Kunal Mukherjee, Dec 04 '17 at 10:05
How? I don't have idea how to use joins in insert statement. — Paras, Dec 04 '17 at 10:09
What sort of table design is this? . What is the point of storing two distinct records in a single row? — Kaushik Nayak, Dec 04 '17 at 10:18

score 0 · Accepted Answer · answered Dec 04 '17 at 10:17

If your DBMS support window functions, you may use them to join your intermediate result appropriately.

select t1.id, t1.country, t1.salary, t2.id, t2.country, t2.salary
from
(
  select *, row_number() over (order by id) rn
  from data
  where country = 'us'
) t1
full join
(
  select *, row_number() over (order by id) rn
  from data
  where country = 'uk'
) t2 on t1.rn = t2.rn

demo

RESULT

id      country salary  id  country salary
-------------------------------------------
1       us      10000   2   uk      25000
3       us      35000   4   uk      31000
null    null    null    5   uk      26000

https://stackoverflow.com/questions/47630095/reconciliation-automation-query ..... can you help with this? — Paras, Dec 05 '17 at 04:44

add two different queries result into one table

1 Answers1

Linked