I have data like below in a table.
| CaseNum | FirstAgent | NextAgent |
|---|---|---|
| 1234 | Rama | Raja |
| 1234 | Rama | Rama |
| 5678 | Siva | Rama |
| 5678 | Siva | Krishna |
My requirement is I need to out put like below.
| Agent | Case Count |
|---|---|
| Rama | 2 |
| Raja | 1 |
| Siva | 1 |
| Krishna | 1 |
I need to show both firstagent and nextagent in a single column.
We should not consider duplicates if the same agent is there in first agent and next agent.
Could some on please help on this.