I want to know if there is a way we can do the below task in SQL. Can we do this with match_ recognize?
| Region | Role | employees |
|---|---|---|
| Africa | Analyst | 2 |
| Africa | S. Analyst | 3 |
| Africa | Manager | 9 |
| India | Partner | 2 |
| India | S. Manager | 7 |
| India | S. Analyst | 10 |
| USA | Analyst | 1 |
| USA | S. Analyst | 2 |
| USA | Partner | 2 |
Above is the input table. I want to group the role for a specific region where No. of employees is less than 6. So, In Africa, we have to combine Analyst, S.Analyst and manager (2+3+9) to overcome the threshold value of 6. Partner and S. Manager must be combined in India (2+7). S. Analyst can be left as it is. We have to drop the USA or add some flag that we can identify using a where clause. So the output should look like below.
O/P
| Region | Role | employees |
|---|---|---|
| Africa | Analyst_S. Analyst_Manager | 14 |
| India | Partner_S. Manager | 9 |
| India | S. Analyst | 10 |