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