How can I merge rows and columns from two sql tables into one table

Question

I searched for a while but I couldn't find something similar.
I have two tables with data and I want to merge those two in two one.

Tbl1
id nr val1
1  a1  123
2  a2  124
3  a3  125

Tbl2
id nr val2
5  a1  223
6  a2  224
7  a4  225

Resulting table should be something like this.

Tbl
nr val1 val2
a1  123  223
a2  124  224
a3  125    0
a4    0  225

Any help would be appreciated. Thanks in advance.

It is MS SQL and I tried union and join. But they don't do.

Tag Database you are using and what have u tried so far, Stack overflow is not free code writing service — Ven
– Ven, Commented Feb 23, 2018 at 10:12
You're going to need a full outer join here, but first show us your query attempt and tell us which database you are using. Stack Overflow is not a homework service. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 23, 2018 at 10:13

Pelin · Accepted Answer · 2018-02-23 10:31:15Z

1

if you use MS SQL then you could try full outer join and Isnull function to get the result you need.

SELECT Isnull(tbl1.nr, tbl2.nr) nr, 
    Isnull(tbl1.val1, 0) val1,
      Isnull(Tbl2.val2, 0) val2 FROM tbl1 FULL OUTER JOIN tbl2 ON tbl1.nr = tbl2.nr

answered Feb 23, 2018 at 10:31

Pelin

9625 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Srini V · Accepted Answer · 2018-02-23 10:26:06Z

0

You can do it by doing a FULL OUTER JOIN and COALESCE the columns together.

SELECT COALESCE(tbl1.nr,tbl2.nr) AS nr,tbl1.val1,tbl2.val2 
FROM tbl1 FULL OUTER JOIN tbl2 
ON tbl1.nr = tbl2.nr;

answered Feb 23, 2018 at 10:26

Srini V

11.5k15 gold badges70 silver badges93 bronze badges

Comments

Thom A · Accepted Answer · 2018-02-23 11:22:49Z

0

SELECT NVL(a1.nr,b1.nr) as nr,
       NVL(a1.val1,0) AS val1,
       NVL(b1.val2,0) AS val2
FROM table1 a1
     FULL OUTER JOIN table2 b1 ON a1.nr = b1.nr
ORDER BY a1.nr;

edited Feb 23, 2018 at 11:22

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

answered Feb 23, 2018 at 10:34

venkatesh

15113 bronze badges

1 Comment

Thom A Over a year ago

Try to use White space when posting SQL. SQL is a multiline language, and posting your entire query on one line can make it very difficult to read. Also, NVL isn't a valid fucntion in SQL Server, so this answer won't help the OP.

Collectives™ on Stack Overflow

How can I merge rows and columns from two sql tables into one table

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related