Hi All,
I have 2 Tables - Table 1 and Table 2
In Table 1 - Mapping Table,
There are 4 fields
| Mapping1 | Mapping2 | Value1 | Value2 |
| A | AA | 99.00% | 344 |
| B | BB | 89.00% | 45 |
| C | CC | 95.00% | 55 |
| D | AA | 94.50% | 65 |
| E | CC | 98.00% | 43 |
| F | DD | 99.50% | 436 |
| G | EE | 98.50% | 655 |
Two of them - Mapping1 and Mapping2 are used to Map different tables.
Also Combination of Mapping1 and Mapping2 forms a uniqueID.
In Table2 - Main Table,
There are 6 fields
| ID | Column1 | Col2_Mapping1 | CompanyName | Column3 | Column4 |
| 1 | 4 | Z | Company1 | 16 | 64 |
| 2 | 5 | B | Company2 | 20 | 100 |
| 3 | 6 | A | Company3 | 24 | 144 |
| 4 | 7 | G | Company4 | 28 | 196 |
| 5 | 2 | J | Company5 | 8 | 16 |
| 6 | 5 | D | Company2 | 20 | 100 |
| 7 | 3 | K | Company4 | 12 | 36 |
| 8 | 5 | G | Company2 | 20 | 100 |
ID is the primary key,
Column1 is the data pertaining to UniqueID
Col2_Mapping1 has the mapping same as Mapping1 in Table1
Column3 is derived from Column1
Column4 is derived from Column1 and Column3
Required Output:
| Mapping1 | Mapping2 | Value1 | Value2 | Sum(Column1) from Table 2 | Count(Column4) from Table 2 | Sum(Column3) from table 2 |
| A | AA | 99.00% | 344 | | | |
| B | BB | 89.00% | 45 | | | |
| C | CC | 95.00% | 55 | | | |
| D | AA | 94.50% | 65 | | | |
| E | CC | 98.00% | 43 | | | |
| F | DD | 99.50% | 436 | | | |
Mapping1 From Table1
Mapping2 From Table1
Value1 From Table1
Value2 From Table1
Sum(Column1) From Table2 ---- How to get this?
Count(Column4) From Table2 ---- How to get this?
Sum(Column3) From Table2 ---- How to get this?
if any Mapping1 (or Col2_Mapping1 in Table 2) doesn't have any value in Column1 then it should be 0.
Attached Sample data.
Hi stalwar1 , Please check if you can help.
Regards,
Anjali Gupta
