Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

combine two rows into one only for rows that meet certain condition

YoohooNov 8 2019 — edited Nov 13 2019

hi everyone,

I have a particular scenario where i want to merge two rows into one but only for specific scenarios.  consider the following data

with table1 as

(

  select 23 cid, 'test' name,  1 type, 123.34 amt,  167 fid,  889 fdb from dual union all

  select 23 cid, 'test' name,  2 type, 345.34 amt,  167 fid,  167 fdb from dual union all

  select 2 cid, 'test45' name,  1 type, 3.34 amt,  16 fid,  89 fdb from dual union all

  select 2 cid, 'test45' name,  2 type, 35.34 amt,  16 fid, 89 fdb from dual union all

  select 2 cid, 'test45' name,  2 type, 2.34 amt,  16 fid,  89 fdb from dual union all

  select 4 cid, 'test2' name, 2 type, 65.34 amt,  0 fid,  0 fdb from dual union all

  select 5 cid, 'test4' name, 2 type, 245.34 amt,  22 fid,  33 fdb from dual

)

, table2 as

(

    select 'ABC' auth,  167 fid,  889 fdb from dual union all

    select 'ABC2' auth,  16 fid,  89 fdb from dual union all

    select 'HDE' auth,  22 fid,  33 fdb from dual union all

    select 'YEW' auth,  4 fid,  6 fdb from dual

)

i want to join table1 and table2  on fid  and fdb columns.  in some cases one row in table2 will match two or more rows in table1 (fid=167, fdb=889 for example) and in some cases one row in table2 will match one row in table1 (fid=22)

and other cases one row in table2 will have no match in table1 (fid4 in table2).  what i want to do merge two rows into one only when one row in table2 matches two or more rows in table1

my output should be as follow:

CID     NAME           TABLE1_TYPE     TABLE2_TYPE2       TYPE1_AMT       TYPE2_AMT      FID     FDB    AUTH

23      TEST                       1                       2                             123.34                345.34                  167     889    ABC

2       TEST45                   1                        2                             3.34                    35.34                    16      89     ABC2

2       TEST45                   1                       2                              3.34                    2.34                       16      89     ABC2 

4       TEST2                                              2                                                         65.34                     0        0 

5       TEST4                                              2                                                         245.34                   22       33    HDE                   

let me explain the logic behind the output.  as mentioned earlier,  i want to join table1 and table2 on fid and fdb columns.  lets take cid=23 for example:

there is one row in table2 with fid=167 and fdb=889  and two rows in table1. one row in table1 has type=1 and the other has type=2.  when there is a type=1 row in table1, i want to merge that row with a type=2 row in table1 that has the same fid and fdb.

this produce the first row in the output.  if there is a type=1 but not matching type=2 row(match on fid and fdb) then no need to merge of course

lets take another example,  CID=2.  there is one row in table2 fid=16 and fdb=89 and for this combination there are 3 rows in table1.  so in this case the type=1 row should be merge with each type=2 row in table1 and therefore you see two rows in the output

cid=4 doesnt have any matches in table2 so it is display as is.   cid=5 has one match in table2 but there is only one row with type=2. there is no type=1 row so row is display as is. 

merging of rows should happen when there is a type=1 row and at lease 2 or more type=2 rows.

i am using oracle 11g.   can someone help me write a query that produce the output above?  i was thinking of using PIVOT but then got stuck because the merge should happen in certain cases as mentioned above.

feel free to ask me any question if i was not clear on my explanation

thanks in advance

This post has been answered by Frank Kulash on Nov 8 2019
Jump to Answer
Comments
Post Details
Added on Nov 8 2019
3 comments
10,983 views