First, we create a table named combine test;

CREATE TABLE combine_test AS

    SELECT 100 eft_no, 4 bank_code, NULL eft_amount, ‘success’ eft_desc FROM DUAL UNION ALL

       SELECT 100, 2, 100, NULL  FROM DUAL UNION ALL

       SELECT 120, 4, NULL, ‘pending’ FROM DUAL UNION ALL

       SELECT 120, 4, 210, NULL FROM DUAL  UNION ALL

       SELECT 200, 3, 200, NULL FROM DUAL;

EFT_NO	BANK_CODE	EFT_AMOUNT	EFT_DESC
100	4		Success
100	2	100
120	4		Pending
120	4	210
150	3	200

Then, we get the bank_name using the table data. Now the output is like this;

EFT_NO	BANK_CODE	BANK_NAME	EFT_AMOUNT	EFT_DESC
100	4			Success
100	2	American Express	100
120	4	American Express		Pending
120	4		210
150	3	American Express	200

As you see; rows with the same eft_no have missing fields comparing to each other.

If we want to make a single-completed  row, table x is grouped by eft_no

and maximum value of the columns wanted to be completed is taken.

SELECT   x.eft_no, MAX (x.eft_amount) eft_amount, MAX (x.bank_name) bank_name, MAX (x.eft_desc)  eft_desc

    FROM  (SELECT   eft_no, bank_code,

                  DECODE(bank_code, 4, ’American Express’,  null) bank_name, eft_amount, eft_desc

                 FROM combine_test

                 GROUP BY eft_no, bank_code, eft_amount, eft_desc) x

GROUP BY x.eft_no

The final output is;

EFT_NO    EFT_AMOUNT       BANK_NAME        EFT_DESC

——–          —————-       —————–          ————-

  200                 200

  120                  210               American Express        pending

  100                 100               American Express        success