How to combine duplicate rows into unique rows in the different fields

Database
Fan

How to combine duplicate rows into unique rows in the different fields

TABLE:MAIN
Key
A
B
C
D
E
F
G

TABLE:SF
key any
A 123
A 456
A 789
B 321
B 654
D 987
D 888

TABLE:LA
key any
A 111
A 222
A 333
A 444
A 555
C 666
D 777

I request the following output:
SF LA
key any any
A 123 111
A 456 222
A 789 333
A 444
A 555
B 321
B 654
C 666
D 987 777
D 888

I tested the following SQL, but it appears duplicate rows in both SF_any and LA_any:
SQL:
Select key, SF_any, LA_any
from TABLE_MAIN MAIN
left outer join TABLE_SF SF
on MAIN_key=SF_key
left outer join TABLE_LA LA
on MAIN_key=LA_key
WHERE NOT (LA_any is NULL or SF_any is NULL)

Please advise if there is any better way to get the above output. Thanks,
2 REPLIES
Enthusiast

Re: How to combine duplicate rows into unique rows in the different fields

See the code below.
This joins SF and LA rows on the basis of same Key, and the lowest Any field in SF is joined to the lowest Any field in LA.
This gives a difference from your expected result set because for Key D, SF Any field 888 joins to LA Any field 777. You show SF Any field 987 joining to LA Any field 777.
You may need to refine (or consistently define) the join rules!

Create Volatile Table "Main"
(PKey Char Not Null)
Unique Primary Index (PKey)
On Commit Preserve Rows
;

Create Volatile Table SF
(PKey Char Not Null
, SF_Any Smallint Not Null)
Primary Index (PKey)
On Commit Preserve Rows
;

Create Volatile Table LA
(PKey Char Not Null
, LA_Any Smallint Not Null)
Primary Index (PKey)
On Commit Preserve Rows
;

Insert Into "Main" Values ('A');
Insert Into "Main" Values ('B');
Insert Into "Main" Values ('C');
Insert Into "Main" Values ('D');
Insert Into "Main" Values ('E');
Insert Into "Main" Values ('F');
Insert Into "Main" Values ('G');

Insert Into SF Values ('A',123);
Insert Into SF Values ('A',456);
Insert Into SF Values ('A',789);
Insert Into SF Values ('B',321);
Insert Into SF Values ('B',654);
Insert Into SF Values ('D',987);
Insert Into SF Values ('D',888);

Insert Into LA Values ('A',111);
Insert Into LA Values ('A',222);
Insert Into LA Values ('A',333);
Insert Into LA Values ('A',444);
Insert Into LA Values ('A',555);
Insert Into LA Values ('C',666);
Insert Into LA Values ('D',777);

sELECT * fROM "Main"
Order By 1;
Select * From LA Order By 1,2;
Select * From SF Order By 1,2;

Select Coalesce(Mn.PKey, DLA.PKey) As PKeyN
, DSF.SF_Any
, DLA.LA_Any
From "Main" Mn
Left Join (Select PKey , SF_Any ,
Row_Number () Over (Partition By PKey Order By SF_Any) AS SF_RowNum
From SF) DSF
On Mn.PKey = DSF.PKey
Full Join (Select PKey , LA_Any ,
Row_Number () Over (Partition By PKey Order By LA_Any) As LA_RowNum
From LA) DLA
On Mn.PKey = DLA.PKey
And (DSF.SF_RowNum = DLA.LA_RowNum
Or DSF.SF_RowNum is Null)
Where Not (SF_Any Is Null And LA_Any is Null)
Order By PKeyN,LA_Any, SF_Any
;

HTH
Fan

Re: How to combine duplicate rows into unique rows in the different fields



It works. This is great demo. Thank you very much.......