1 Reply Latest reply: Jul 30, 2013 10:02 AM by BrendanP RSS

    Group Similar Records together

    1009115

      Version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

       

      The same Question has been asked yesterday :

      Identify Different Groups in the Organization (Group the Same data together)

       

      Help me in Identifying the groups(different Sectors) in the Organization

      Each Group will have the same Group id if  We_org_id , We_addr_id or We_org_id , Ein Matches

      In the below Example Row 1 and 2 Are linked with We_org_id , We_addr_id

      and row 2 and 3 are linked with We_org_id , Ein  so all the three rows has the same groupid

       

       

      Please give me some suggestions on how to proceed.... i will try to program.

       

      Included Output Required.

      Example

      311    563          72-1500000    2

      311    563          72-1500001    2

      311    565          72-1500001    2

       

       

       

      -- Table and inserts

          CREATE TABLE ORG_MISMATCH  
          (  
            WE_ORG_ID   NUMBER,  
            WE_ADDR_ID  NUMBER,  
            EIN         VARCHAR2(30 BYTE)  
          );  
          SET DEFINE OFF;  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 24303142, '31-1700059');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 56381251, '39-1675361');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 56381251, '31-1700059');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 56381251, '26-0060245');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 56381251, '72-1284709');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 121786868, '31-1700059');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 21053495, '72-1355929');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 56381251, '73-1317052');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 56381251, '56-2525845');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 56381251, '72-1355929');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 56381251, '84-1535762');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 56381251, '91-2031795');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 56381251, '84-1487943');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 56381251, '91-2035844');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 56381251, '84-1535753');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 56381251, '72-1501788');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 56381251, '30-0137738');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 563, '72-1500001');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 565, '72-1500001');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 56381251, '72-1355929');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 24303142, '31-1700059');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 56381251, '31-1700059');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 121786868, '31-1700059');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 56381251, '84-1535762');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 56381251, '72-1501788');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 56381251, '91-2031795');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 56381251, '30-0137738');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 56381251, '72-1284709');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 56381251, '56-2525845');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 56381251, '91-2035844');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 21053495, '72-1355929');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 56381251, '39-1675361');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 56381251, '84-1487943');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 56381251, '84-1535753');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 56381251, '26-0060245');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (999, 56381251, '73-1317052');  
          Insert into ORG_MISMATCH  
             (WE_ORG_ID, WE_ADDR_ID, EIN)  
          Values  
             (311, 563, '72-1500000');  
          COMMIT;  
      

      Out put Required

       

      WE_ORG_ID    WE_ADDR_ID    EIN    GROUPID

       

      311    24303142    31-1700059    1

      311    56381251    39-1675361    1

      311    56381251    31-1700059    1

      311    56381251    26-0060245    1

      311    56381251    72-1284709    1

      311    121786868    31-1700059    1

      311    21053495    72-1355929    1

      311    56381251    73-1317052    1

      311    56381251    56-2525845    1

      311    56381251    72-1355929    1

      311    56381251    84-1535762    1

      311    56381251    91-2031795    1

      311    56381251    84-1487943    1

      311    56381251    91-2035844    1

      311    56381251    84-1535753    1

      311    56381251    72-1501788    1

      311    56381251    30-0137738    1

      311    563       72-1500001    2

      311    565       72-1500001    2

      311    563       72-1500000    2