I understand that, but there needs to be some rules before an answer can be given. I see that S1 needs to match C1, S2 needs to match C2 and S3 needs to match C3 in your example. However, I presume that they are example values. I also understand that nulls are not required. What is the rule that prevents S1 matching C2 and C3, S2 from matching C1 and C3 and S3 from matching C1 and C2? The only join field you have is the Oppr_Id. We need the rules and how they should work with real data.
I can tell you how to get the result you have got. You match on Oppr_Id and then take number character from the other two fields and match on numbers. To do this you could use substring(). But I sense that is not what you really want.
Thanks for you reply. Below is the scenarios : 1. Opportunities is related to services 2. Opportunities is related to contacts 3. Opportunities is related to roles There is no relation between services ,contacts & roles. When am trying to arrange this am getting kind of cross product that means multiple records. So how I need the output is based on the maximum count of either services or contacts or roles, I need the opportunities count.
For example: S services is 4, R roles 2, Ccontacts 2 So how my output looks
Can you give an example like the first one, but with all of the rules. You have 4 columns in your explanation, but only 3 in your original example. Also, your rules are not shown by your explanation. You say....
"1. Opportunities is related to services 2. Opportunities is related to contacts 3. Opportunities is related to roles"
