CODEWITHSUNDEEP
sqloracle-databaseplsql
Pointer
Pointer

Reputation: 2186

Merge two row in one from different table

Below is simple example, I need in one row display value from different table.

eg.

Create table table_1
(
    id number,
    name varchar2(100),
    emp varchar2(100) ,
    num_1 number,
    num_2 number,
    num_3 number,
    num_4 number
);


Create table table_2
(
    id number,
    emp varchar2(100),
    num_1 number,
    num_2 number,
    num_3 number,
    num_4 number
);

Insert into table_1 (id,name,emp,num_1,num_2,num_3,num_4) values (1,'AUDI','MARC',8,8,null,null);
Insert into table_2 (id,emp,num_1,num_2,num_3,num_4) values (2,'MARC',null,null,7,7);

need resaults as:

name   emp   num_1 num_2 num_3 num_4
----------------------------------------
AUDI   MARC  8     8     7     7

Upvotes: 0

Views: 79

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

With sample data you posted, that's join with coalesce.

SQL> SELECT a.name,
  2         a.emp,
  3         COALESCE (a.num_1, b.num_1) num_1,
  4         COALESCE (a.num_2, b.num_2) num_2,
  5         COALESCE (a.num_3, b.num_3) num_3,
  6         COALESCE (a.num_4, b.num_4) num_4
  7    FROM table_1 a JOIN table_2 b ON a.emp = b.emp;

NAME   EMP         NUM_1      NUM_2      NUM_3      NUM_4
------ ------ ---------- ---------- ---------- ----------
AUDI   MARC            8          8          7          7

SQL>

Upvotes: 1

Related Questions